convert time into decimal

[Richard]

=IF(E44="","X",E40-E44)

the formula above where E40 is the time now, and E44 is the time of 2 hours
ago. I want to return 2 instead of 2:00 PM. How do I do this? I do this
but it does not work (E40-E44)*24.

 

[JE McGimpsey]

When you say (E40-E44)*24 "doesn't work", what do you mean?

Do you get the wrong value? An error? A crash?

If E40 and E44 are both XL times (e.g., time stored as fractional days,
and displayed as hh:mm or hh:mm:ss), then (E40-E44)*24 should work
correctly (make sure you format the result cell as General or a Number
format.

How are you populating E40 and E44? If you're using the NOW() function,
note that it returns the date (as the integer offset from a base date)
as well, so if

E40: =NOW()
E33: 2:00

then your result will be very large.

 

[Richard]

How are you populating E40 and E44? If you're using the NOW() function,
note that it returns the date (as the integer offset from a base date)
as well, so if

E40: =NOW()
E33: 2:00

then your result will be very large.
Correct, E40 is a now function and E44 was the time of 2 hours ago. I
format the cell so that there are no AM or PM display after 2:00, but I
don't want display 2:00, I want 2 instead. Based on your explaination, it
seemed like I cannot convert the result into decimal if I use the now
function?

 

[Roger Govier]

Hi Richard

Based on your explanation, it seemed like I cannot convert the result
into decimal if I use the now function?

No, that's not what JE was saying.
NOW() is 25 Jan 2007 18:26 (here in the UK
If you are comparing with 2:00 (with no date) then Excel will interpret
that as 00 Jan 1900 02:00 and the total number of hours between the two
values will be very large (around 940,000)

so use
=(E40-(TODAY()+E44))*24
or
=(MOD(E40,1)-E44)*24

to gain your correct answer.

 

[JE McGimpsey]

You still don't say how you're entering "the time of 2 hours ago". Are
you entering it as a date and time, like Now() does? Then the
calculation will work.

If you're just entering the time (e.g., 3:00 or 0.125, since 3:00 is 1/8
of 1 day), then you'll need to use just the fractional part of NOW(),
e.g,

E40: =MOD(NOW(),1)

formatted as a time.

Then your time difference will work. If your times span midnight, then
use

=MOD(E40-E44,1)*24

How are you populating E40 and E44? If you're using the NOW() function,
note that it returns the date (as the integer offset from a base date)
as well, so if

E40: =NOW()
E33: 2:00

then your result will be very large.
Correct, E40 is a now function and E44 was the time of 2 hours ago. I
format the cell so that there are no AM or PM display after 2:00, but I
don't want display 2:00, I want 2 instead. Based on your explaination, it
seemed like I cannot convert the result into decimal if I use the now
function?[/QUOTE]