Recording and Monitering Times

[rossb1992]

So I moniter closing times, however im having an issue with times of closing around midnight.

For example the actual closing time of a store should be 00:00. if it closes early before midnight the formula doesnt work. Can anyone help. I get this

Cell D21 is Scheduled closing of 00:00
Cell D22 is Recorded actual closing time of 22:56
Cell D23 is variance in mins =IF(D22=0,0,(D22-D21)*1440)

Can anyone help as it works for all other times?

Cheers
Ross

[mehmetcik]

Try this instead:

=IF(HOUR(E21)<12,HOUR(E21)*60+MINUTE(E21),1440-HOUR(E21)*60+MINUTE(E21))

[rossb1992]

Try this instead:

=IF(HOUR(E21)<12,HOUR(E21)*60+MINUTE(E21),1440-HOUR(E21)*60+MINUTE(E21))

Thank you for this im slightly confused by the E21 cell. I realize this is just an example but I can't seem to figure out the right way to apply it

[bebo021999]

Excel thinks that D22 is later than D21 22 hours 56 mins

Let says if early closing hour is around 12PM to 23:59 PM previous day, late closing hour around 00:00 AM to 11:59AM next day

D23:

=IF(HOUR(D22)<12,D22-D21,"-"&TEXT(D21+1-D22,"hh:mm"))

[rossb1992]

Excel thinks that D22 is later than D21 22 hours 56 mins

Let says if early closing hour is around 12PM to 23:59 PM previous day, late closing hour around 00:00 AM to 11:59AM next day

D23:

=IF(HOUR(D22)<12,D22-D21,"-"&TEXT(D21+1-D22,"hh:mm"))

Hi THere,

Thanks for replying. However I should have said I need to calculate the amount of time in minutes that the store is early/late?

So if it closes 30 mins early -30
Closes 30 mins late +30

[joeu2004]

So I moniter closing times, however im having an issue with times of closing around midnight. For example the actual closing time of a store should be 00:00. if it closes early before midnight the formula doesnt work.
[....]
Cell D21 is Scheduled closing of 00:00
Cell D22 is Recorded actual closing time of 22:56
Cell D23 is variance in mins =IF(D22=0,0,(D22-D21)*1440)

You have an unresolvable ambiguity in your notation. If the actual closing time were 00:04, the formula would work. The point is: how can we (and Excel) know whether 00:00 is the midnight before or the midnight after the actual closing time?

Best solution: record the date with the time. Note that midnight is actually the beginning of the next day. So if today is Feb 1, closing time at midnight "today" is actually 00:00 on Feb 2.

PS.... Even though you include the date, you can hide it by formatting the cell as Custom hh:mm , if you prefer.

Then the difference in minutes is simply:

=ROUND((D22-D21)*1440,0)

I use ROUND(...,0) to eliminate binary arithmetic anomalies that arise because time is represented as a decimal fraction, and most decimal fractions cannot be represented exactly in binary.

[bebo021999]

Assuming that your actual closing time is around +/- 12 hours from 00:00

With my formula in #3:

D21=00:00

* If late :

D22=02:00

==> D23=02:00

* If early:

D22=23:00

==> D23= -01:00

Note that excell gets problem with nagative time, so D23 = "- 01:00" is text displayed as number. In case you wish to use D23 for calculation, i.e adding with D25,try to convert D23 to real number: =D25 + IF(LEFT(D23)="-",-MID(D23,2,5),MID(D23,2,5))

[mehmetcik]

try this in D23

=IF(ABS(HOUR(D21)*60+MINUTE(D21)-(HOUR(D22)*60+MINUTE(D22)))>1220,1440-ABS(HOUR(D21)*60+MINUTE(D21)-(HOUR(D22)*60+MINUTE(D22))),ABS(HOUR(D21)*60+MINUTE(D21)-(HOUR(D22)*60+MINUTE(D22))))