Calculate elapsed time by now() ?

**Desiv5** · 05-06-2010, 02:46 AM

Hello everybody,

I am trying to calculate the working time and the "elapsed" / "to go" time. For example, with start time at 08:00 AM and a work time of 8:00 hours, I will calculate the "check-out" time using the sum "start time" + "work time" and obtain the result ("check-out" time) as 16:00. This is functioning ok, but now I would like to calculate the difference between the current time and the "start time" and "end time" and I'm doing this using the function NOW() in a new cell and making the difference between this new cell and the start- and end-time cells. In this case I obtain wrong results!
For example:
(A3) 08:00
(B3) 16:00
(C3) NOW() --> in this case 08:42

(A4) = (C3) - (A3) => (A4) 00:42 (ok)
(B4) = (B3) - (A3) => (B4) ######## (negative, wrong!)

Where is the mistake?

Thank you in advance for your answers!

**DonkeyOte** · 05-06-2010, 02:55 AM

NOW() returns a DateTime stamp - not just Time - you no doubt have the cell formatted as Time which is why you don't see the Date.

Modify C3 to be: =MOD(NOW();1)

then your calcs should work (until such time as the Time exceeds End Time or precedes Start Time of course - not clear what the intention is in this scenario)

**Desiv5** · 05-06-2010, 03:50 AM

Thanks a lot for your answer, DonkeyOte, it works!
Concerning the scenario where the current Time exceeds the defined range, I would like to cover also this case, maybe using a conditional function (IF) to display a "-" sign in front of the result when necessary, could you give me a hint also here?

Thank you again!

**DonkeyOte** · 05-06-2010, 03:59 AM

Can you first clarify the intent behind the calc in B4 - is it to calc the difference in time between Current Time and End Time or simply the difference between End Time & Start Time - the narrative in your first post and formula don't quite tie out.

Maybe a good idea to outline desired results by means of examples.... consider a start time and end time of 08:00/16:00 ... what would be the results should current time be:

a) 23:00
b) 04:00
c) 18:00

If you outline results for both A4 & B4 based on above values that would help I think.

Will Start & End times ever cross midnight - eg 20:00 / 04:00 ?

**Desiv5** · 05-06-2010, 05:43 AM

Sorry, my mistake: B4 should give the remaining time until NOW() and end time, so
(B4) = (B3) - (C3) (not B3 - A3)

What I need is to get every time the "distance" from NOW() to start time (so the elapsed time until now) and to end time (so the "time to go"). This "distance" should also be able to be negative, for example for the case I make over-time. The C3 cell will contain the current time (NOW()). And no, Start and End times will not cross midnight.
I hope I was a little bit more clear now, as I've also corrected the mistake in my first post.

**DonkeyOte** · 05-07-2010, 05:16 AM

Apologies for delay in replying I was not really around yesterday.

You will find on a PC that though time is just a decimal value (ie 12 hours = 0.5 and -12 hours = -0.5 hours) you can not display negative Time value in Time Format (on 1900 date system - you will see ######## etc...).

Without knowing what you want to use A4/B4 for you might find that returning the value as a text string will be sufficient - eg:

A4: =REPT("-";$C$3<$A$3)&TEXT(ABS($C$3-$A$3);"hh:mm")
B4: =REPT("-";$B$3<$C$3)&TEXT(ABS($B$3-$C$3);"hh:mm")