Converting Time to Decimals

[Dare Dukes]

Hi. I'm a freelancer, and I'm trying to create a time sheet in Excel for
Mac v.X (teachers and students edition) to keep track of my time worked (for
me and clients).

I want two columns, one showing my start time, the other showing my end
time. And I'd like a third column that shows the total time worked
converted to a decimal. E.g.:


Start End Total
4:15 6:30 2.25


A couple of problems. First, I can't seem to figure how to set up a formula
for getting the total time worked--specifically, I can't figure out how to
tell excel that, when subtracting the first column from the second, it's
subtracting time values. Second, I haven't the slightest clue (after
searching through the default/factory formulas) how to get the value
converted to a decimals.

All help would be appreciated.

--dd

 

[JE McGimpsey]

A couple of problems. First, I can't seem to figure how to set up a formula
for getting the total time worked--specifically, I can't figure out how to
tell excel that, when subtracting the first column from the second, it's
subtracting time values.

No need - XL stores times as fractional days, so 3:00 = 3/24 = 1/8 =
0.125.

You can add and subtract times as needed:

A1: 4:15
B1: 6:30
C1: = B1 - A1 ===> 2:15

when C1 is formatted as a time. Note that if the times span midnight,
the earlier time (e.g., 9:00, or 0.875) will be greater than the later
time (e.g., 3:00, or 0.125), so you need to add 1 to the later time
(e.g., 1.125 - 0.875 = 0.25, or 6:00). Fortunately, XL converts boolean
TRUE/FALSE values to 1/0, respectively:

C1: = B1 - A1 + (B1<A1)

You can also use the MOD function to accomplish the same thing:

C1: =MOD(B1-A1,1)

Second, I haven't the slightest clue (after
searching through the default/factory formulas) how to get the value
converted to a decimals.

Since times are fractional days, you just need to multiply the time by
24:

C1: =(B1-A1+(B1<A1)) * 24

or

C1: =MOD(B1-A1,1) * 24