Length of time

[Mike Dobony]

I am trying to keep track of time spent on a job. I put in the start time
and the stop time. Individual day tallies are fine, but when I try to get a
total I have 00:50 right now. How can I go from clock time to length of
time? Thanks

--
Mike D.

www.stopassaultnow.org

Remove .spamnot to respond by email

 

[Arvi Laanemets]

Hi

Format the cell as Custom "[h]:mm"


Arvi Laanemets

 

[Mike Dobony]

Hi

Format the cell as Custom "[h]:mm"

Nope. This is what I still get:

2:15 4:15 2:00
11:35 13:45 2:10
1:25 18:45 17:20
1:25 9:25 8:00
Total Hours 5:30

 

[Ron Rosenfeld]

Format the cell as Custom "[h]:mm"

Nope. This is what I still get:

2:15 4:15 2:00
11:35 13:45 2:10
1:25 18:45 17:20
1:25 9:25 8:00
Total Hours 5:30

You did not format the cell with the totals as Arvi instructed. Specifically,
you did not place the brackets [ ] around the h.

If you don't do that, the hours "roll over" every 24 and give you the above
result.

When I paste your data into my worksheet AND format it properly (and using a
SUM formula for total hours):

2:15 4:15 2:00
11:35 13:45 2:10
1:25 18:45 17:20
1:25 9:25 8:00
Total Hours 29:30


--ron

 

[Mike Dobony]

Format the cell as Custom "[h]:mm"

Nope. This is what I still get:

2:15 4:15 2:00
11:35 13:45 2:10
1:25 18:45 17:20
1:25 9:25 8:00
Total Hours 5:30

You did not format the cell with the totals as Arvi instructed. Specifically,
you did not place the brackets [ ] around the h.

If you don't do that, the hours "roll over" every 24 and give you the above
result.

When I paste your data into my worksheet AND format it properly (and using a
SUM formula for total hours):

2:15 4:15 2:00
11:35 13:45 2:10
1:25 18:45 17:20
1:25 9:25 8:00
Total Hours 29:30


--ron

Yup! Thanks! Now can I convert 29:30 to 29.5 easily?

 

[JohnI]

Mike,

Yeah. Multiply your answer by 24 (i.e. hours in a day) and reformat your
cell back to a number format.

it should now show 29.5



regards,

JohnI

Format the cell as Custom "[h]:mm"



Nope. This is what I still get:

2:15 4:15 2:00
11:35 13:45 2:10
1:25 18:45 17:20
1:25 9:25 8:00
Total Hours 5:30

You did not format the cell with the totals as Arvi instructed. Specifically,
you did not place the brackets [ ] around the h.

If you don't do that, the hours "roll over" every 24 and give you the above
result.

When I paste your data into my worksheet AND format it properly (and

using

a
SUM formula for total hours):

2:15 4:15 2:00
11:35 13:45 2:10
1:25 18:45 17:20
1:25 9:25 8:00
Total Hours 29:30


--ron

Yup! Thanks! Now can I convert 29:30 to 29.5 easily?

 

[Dave Peterson]

Try multiplying by 24 and then format that cell as general:

If you formatted the cell as General (instead of [h]:mm), you'd see that excel
actually stored that value as:
1.22916666666667

Which means that it's really 1.22 days long. Multiplying by 24 converts it to
hours.

Format the cell as Custom "[h]:mm"



Nope. This is what I still get:

2:15 4:15 2:00
11:35 13:45 2:10
1:25 18:45 17:20
1:25 9:25 8:00
Total Hours 5:30

You did not format the cell with the totals as Arvi instructed. Specifically,
you did not place the brackets [ ] around the h.

If you don't do that, the hours "roll over" every 24 and give you the above
result.

When I paste your data into my worksheet AND format it properly (and using a
SUM formula for total hours):

2:15 4:15 2:00
11:35 13:45 2:10
1:25 18:45 17:20
1:25 9:25 8:00
Total Hours 29:30


--ron

Yup! Thanks! Now can I convert 29:30 to 29.5 easily?

 

[Ron Rosenfeld]

Yup! Thanks! Now can I convert 29:30 to 29.5 easily?

Multiply by 24.

Excel stores times as fractions of a day.


--ron

 

[Mike Dobony]

Multiply by 24.

Excel stores times as fractions of a day.


--ron

Cool! Thanks everyone! Why can't they just say that in the help file!?
That thing can be so vague about things sometimes.

 

[Dave Peterson]

You want to learn about time and dates?

Try this:
http://www.cpearson.com/excel/datetime.htm

Chip Pearson has lots of info here.