How do I subtract time over midnight the previous day from a vlookup result

Hi,

Cell C72 has the time 00:27. I have data from a vlookup table in cell O 72 of 05:30 (this is hours and minutes not time units am/pm).

I would like to subtract the 05:30 from the time 00:27 and have the result shown in time in cell O 72.

I can do this for times before midnight using:

=ABS(VLOOKUP($A72,'Sheet 1'!$A$3:$E$183,5,TRUE)-C72)

When I use the above and the time I need to subtract from is after midnight I get 05:03 (basically it has only taken 27 minutes off)

Any help would be appreciated.

.
00:27 and 05:30 are in the same day ?

If not, include the date along with the time for each cell.

Thanks for trying but it returned the same result

Originally Posted by Logit

.
00:27 and 05:30 are in the same day ?

If not, include the date along with the time for each cell.

Hi Logit,

The 00:27 is the end of a job from the previous day which goes past midnight. I need to deduct the result of the Vlookup table (in the case 5 hours and 30 minutes) from 00:27 in the same cell O 72. The answer I need is 18:57.

Thanks

Hi ,

All time values are numbers between 0 and .99999 , where 0 is midnight , and .99999 is 23:59:59

Also , you cannot do time arithmetic which will result in a negative value.

Thus , when your time values roll over midnight , just add 1 before you take the difference or subtract.

Thus , when you wish to subtract five and a half hours from 00:27 , you need to add 1 before you carry out the subtraction.

1 + "00:27:00" - "05:30:00" will give you 18:57.

Narayan

Originally Posted by AliGW

Pleae see post #5 - we need to see this in context.

I am having trouble sending it lol

Try this:

=C72 - VLOOKUP($A72,'Sheet 1'!$A$3:$E$183,5,TRUE)-C72 + (VLOOKUP($A72,'Sheet 1'!$A$3:$E$183,5,TRUE)>C72)

Hope this helps.

Pete

Originally Posted by AliGW

Try this (based on post #8 above):

=IF(B2<C2,0,1)+ABS(VLOOKUP($A2,'Sheet 1'!$A$3:$E$183,5,TRUE)-C2)

Lol it still doesnt work it says 18:18. it is supposed to be 5 hours and 30 minutes from 12:27am minutes which should = 18:57

Originally Posted by Pete_UK

Try this:

=C72 - VLOOKUP($A72,'Sheet 1'!$A$3:$E$183,5,TRUE)-C72 + (VLOOKUP($A72,'Sheet 1'!$A$3:$E$183,5,TRUE)>C72)

Hope this helps.

Pete

No Sorry Pete, It left me with a static 18:30

Just put:

=VLOOKUP($A72,'Sheet 1'!$A$3:$E$183,5,TRUE)

in a nearby empty cell (formatted as time), so you can see what you are actually subtracting from C72.

Hope this helps.

Pete

Hi ,

I wanted OP to first confirm that this is correct.

I have edited my post to cater to the normal , positive values.

Narayan

Hi Naryan,

It worked for the times going just over midnight.

As I go further into the early hours time just starts ticking up. i.e. 27: 28:

Hi ,

If you can post a file which has as much data as possible , it will make it easier to test for all possible situations.

Otherwise , try the other suggestions and see if they work.

Narayan

HI AliGW,

You did it, thank you so much.

Kind regards

When I put this formula in Cell O3 in the file you uploaded in Post #9:

=C3-VLOOKUP($A3,'Sheet 1'!$A$3:$E$183,5,TRUE)+(VLOOKUP($A3,'Sheet 1'!$A$3:$E$183,5,TRUE)>C3)

I get the result of 18:57.

Pete