ETA formula

[Ken]

Hey all,

I'm trying to come up with a formula that will take the current time and
subtract down to 0:00 giving an argument of an expected time an event will
happen using the 24 hour clock. Example:

A B
1 Arrival Time Remaining
2 17:00 (Current time - cell A2)

Using the =NOW() function - A2 is not working. What am I doing wrong? I've
tried formatting the time using Custom to no avail.

Cell A2 would be the time entered in manually and cell B2 the destination
cell with the formula. Please help! TIA

 

[Jordon]

Format A2 using date and time and you'll see it's probably 1/0/1900 5:00 PM.

You could always...
Put =today() in A1 and hide it.
Put 17:00 in B1.
Put =A1+b1 in C1 and hide it.
Put =now() in D1
Put =C1-D1 in E1

But then again I always do things the hard way. I'm sure someone else has a
more elegant solution.

Jordon

 

[Ken]

Thanks Jordon, I'll give a shot.

 

[Ken]

It works great Jordon! Fantabulous!! Only problem is; arrivals for the next
day. I get the infamous ####### when those times are entered. I hate to
impose but, how do we accomplish that? For example: 04:45 the next day.
Thanks again bud. You're a life saver!

 

[Jordon]

That's easy if every time you enter a date and time together.

A1 =now()
B1 date & time (4/13/05 17:00)
C1 =B1-A1 (formatted [hh]:mm)

"Ken" wrote

 

[Ken]

Cool, that worked too. Thank you sir