Calculating Time After Midnight and Convert Timezone

[purevibe]

Hi,

I'm creating a schedule for work and have an issue calculating the time difference if past midnight, then converting it to EST. The thing that baffles me is it's fine in Google sheets, but I get ######### in Excel.
Screen Shot 2017-10-30 at 20.44.29.png

I've linked the sheet to my google drive here.


Thanks for your help in advance!!!

[Richard Buttrey]

It's better if you upload the workbook here.

Are you trying to calculate the Fonish time from a start time & duration, or calculate a duration from start/end times?

The way I always approach this common problem is to add an IF function that tests for the end time being < than the start time and if so add 24 hours, the default being add zero hours.

[MarvinP]

Hi purevibe and welcome to the forum,

Excel has a problem with negative time or negative dates. Try this...

=If (Answer < 0, -Answer, Answer)
or use the ABS() function
=ABS(Answer)

https://support.microsoft.com/en-us/...signs-in-excel

[CK76]

Excel when dealing with DateTime values, will not accept negative values.

From the looks of it, Google sheet has logic built in to wrap negative time into previous 24 hour cycle.

What you can do is add date portion to time value to avoid negative time value. Or when the calculation results in negative value, add 1 (i.e. 24 hours) to time value.

=IF(C9="","",IF((C9-'Schedule 16 Oct'!$B$13)<0,C9+1-'Schedule 16 Oct'!$B$13,C9-'Schedule 16 Oct'!$B$13))

[daddylonglegs]

You can use MOD function to get the correct duration between two times even when the period crosses midnight, e.g. with start time in A2 and end time in B2

=MOD(B2-A2,1)

That will also work when subtracting hours for timezone change, e.g.

=MOD("01:00"-"4:00",1)

format as time to get 21:00

[kvsrinivasamurthy]

Try this, simple one

If C2 is start time and D2 is finish time

=IF(D2>=C2,D2-C2,1-C2+D2)

Format as

Custom --> [h]:mm

[purevibe]

Hi all,

Thanks for the greeting and your help on this, everyone. I've used the formula from @CK76, which was great and noted the issue with Excel and negative time.