Calculate time remaining

[WendyvdV]

I would like to use a formula to count the time remaining. Is this possible?
Eg:

Say it is 9:15 AM right now and I work untill 17:00 pm, I would like a formula that gives me the work time remaining for today?

And if I have that calculated, I would like to use the field of the remaining work time like this:

Remaining working time: (17:00 - 9:15) = cell c1
A B
Task 1: 0:30
Task 2: 1:00
Task 3: 0:45

Time left for other tasks = cell c1 - sum(b2:b4)

Is this possible?

[6StringJazzer]

If your end time is in D1 then the formula in C1 for time remaining is

=D1-NOW()


Time left after Tasks 1-3 are allocated is in the formula you have already shown:

=C1-SUM(B2:B4)

[WendyvdV]

Hi I tried your solution but the formula D1-now() gives a result of #######
So that does not seem to work? I have in D1 the time 17:00 (uu:mm) and also in the cell where the formula is displayed this is the format of the cell. Am I doing something wrong?

[ELeGault]

Format the now to be in the same format... =D1-Value(Text(Now(),"HH:MM"))

Keep in mind once this goes negative (Time has passed) then it will ######## out... So maybe wrap an if in there...

Please Login or Register  to view this content.

[WendyvdV]

=D1-Value(Text(Now(),"HH:MM"))

In D1 I have value 17:00
Is that all I need to fill in? Because if I do, I now get the result #VALUE

[ELeGault]

TR.xlsx

Take a look... seems to be working fine with the full formula

Please Login or Register  to view this content.

Added seconds for fun

[tigertiger]

=D1-Value(Text(Now(),"HH:MM"))

In D1 I have value 17:00
Is that all I need to fill in? Because if I do, I now get the result #VALUE

Plz try to use the solution

Please Login or Register  to view this content.

[ELeGault]

That will do the trick as well, but again, wrap it up in an IF statement

Please Login or Register  to view this content.

[WendyvdV]

Attachment 379859

Take a look... seems to be working fine with the full formula

Please Login or Register  to view this content.

Added seconds for fun

If I open the file I see the result as time, as soon as I put editing on, it says #VALUE?

=IF(D1<(Now()-Today()),0,D1-(Now()-Today()))
That works! Thanks!