Calculating Elapsed Time

[Rosemary]

Hi,

We have MS Office 2003, Windows XP, in a network environment.

I have a worksheet that calculates time elapsed. Currently, the result is
displayed as hours and minutes. I would like it displayed as a decimal value
instead.

For example, here are my columns --

Date/Time Started
1/5/09 4:50 PM

Date/Time Ended
1/5/09 6:05 PM

Time Elapsed:
1:15

Formula for Time Elapsed column:
=TEXT(C30-B30,"h:mm")

As you can see, time elapsed is displayed as 1 hour 15 minutes. Instead, I
would like it displayed as a decimal value: 1.25

How can I adjust my formula to do this?

Note: Time elapsed will never exceed 12 hours. Our workday starts at 8am
and ends at 9pm (2 shifts), so all results would be within one 24-hour period.

Many thanks,
Rosemary

 

[Dave Peterson]

First, I would have used:

=C30-B30
and just used a custom number format.

But to answer your question:
=(c30-b30)*24
and format as General

or something like:
=text((c30-b30)*24,"0.00")

 

[Bob Bridges]

The key to Dave's answer, Rosemary, is that Excel displays times and dates as
numbers that express days and fractions of days. A date and time is
expressed as the number of days since, um, I think it's 1900-01-01, with Jan
1, 1900 being the zero day and noon of 19000-01-02 being equal to 1.5, that
is, one and a half days since the beginning of Time.

So by taking the same time expressed in days, and multiplying it by 24, you
get the same amount of time in hours; that 1.5 days in the above example
would become 36 hours after multiplying it by 24. If you want to express
elapsed time in minutes just multiply it by 1440, and so on.