Calculate time

[jpac]

Hi,

I'm trying to find a formula to calculate the time between delivery created time and post goods issue time . pls see attached filesheet


wave time time PGI
1 3:30 7:30 10:15
2 7:30 9:30 12:15
3 9:30 11:30 14:15
4 11:30 13:30 16:15
5 13:30 15:30 18:15

delivery order created between 3:30 to 07:30 is wave 1 as above table , the targeted time for shipping out (PGI) is 10:15 for wave 1 and 12:15 for wave 2 . pls help to calculated which all deliveries have failed and passed .


Thanks for help on this.

Rgds

[belle08]

Hi, are the waves already there? or should it be a formula as well?
If we should take PGI into consideration only and assume that the 'wave' is already there, you could place the table above in the sheet and use vlookup using the wave as reference to compare for the PGI.

[jpac]

thank you so much for the reply, yes waves should be in formula .also ontime column should be in formula Pass/fail)

for eg , pass /fail is determine by the pgi time . for wave 1 the pgi time is 10:15 if exceed then fail. hope im explaining clearly .
wave 2( pgi time is 12:15) same shown above table . is ther any chance to create formula to sort this ?

thanks & appreciate you help

jpac

[belle08]

hi jpac,

2 questions:
1. where should we refer wave column from? i see you have 2 wave columns (col A and P) should both be there?
2. also, there are some items that confuses me like row 4, it says wave 1 but the PGI time exceeds 10:15. is this the correct answer?

i tried to play around with the file and this is what i have (placing a formula in the yellow cells) although it gives a different result than the manual calculation. let me know if there are still other criterias that we need to consider

[jpac]

thank you

1. where should we refer wave column from? i see you have 2 wave columns (col A and P) should both be there?

pls ignore column A and B , pls refer waves between column J K & M N .also see the table of waves time on column u,v w

2. also, there are some items that confuses me like row 4, it says wave 1 but the PGI time exceeds 10:15. is this the correct answer?

wave 1 starts from 15:30 (03:3pm) to next day 07:30 am ) . pls do check the delivery crt date(column J ) and pgi date( column n)
im unable to open the file to look the formula , would you pls copy the formula on the thread or can you send to my email Id jpac_0930@hotmail.com

Great help

jpac

[belle08]

Try this. Place this formula in column R but make sure that column V is formatted as text since column P (wave) is in text and not numbers.

Please Login or Register  to view this content.

[olasa]

This may help.
//Ola

[jpac]

Thank you so much belle08 and ola ! . i'll go through and revert the results ..

[olasa]

When you are happy with the solution mark the thread as Solved.
Under the Yellow bar up on top, Right under Thread Tools.
//Ola