That worked for me. Thanks Biff!
T. Valko wrote:
Re: How to Sum Time in Hours & Minutes
13-Mar-08
Try formatting the formula cell as [h]:mm
The brackets keep the hours from rolling over into days.
8:00
8:00
8:00
=SUM(A1:A3) formatted as h:mm = 0:00. Formatted as [h]:mm = 24:00
--
Biff
Microsoft Excel MVP
Previous Posts In This Thread:
How to Sum Time in Hours & Minutes
Hi all,
I run a dayhome and am trying to make my life easier on myself with my
attendance sheet but can't figure out why the formula wont work.
I am trying to get the sum of hours and minutes each child is here during
a
month so I can easily figure out what to charge the parents.
The problem I seem to be having is the total is always more than 24 hours
so
if I use the formula =SUM(D14
20) the total only comes up as 0:00:00. I
believe I read that, that specific formula is only good when the total is
less than 24 hours.
Hopefully this is making sense, does anyone know how to make this work?
Thanks
Re: How to Sum Time in Hours & Minutes
Try formatting the formula cell as [h]:mm
The brackets keep the hours from rolling over into days.
8:00
8:00
8:00
=SUM(A1:A3) formatted as h:mm = 0:00. Formatted as [h]:mm = 24:00
--
Biff
Microsoft Excel MVP
Hi Biff,So I right clicked and went to custom format cell and tried that
and
Hi Biff,
So I right clicked and went to custom format cell and tried that and no
change. I tried added it to the equation and it gave me the value error.
Any other ideas?
Teri
:
The times you have entered may not be true Excel times.
The times you have entered may not be true Excel times. True Excel times
are
just numbers formatted to look like time. For example, 12:00 PM (or 12:00)
has a true numeric value of 0.5.
Assume your range of times is in D14
20. If every cell has a true Excel
time in it then this formula will return 7:
=COUNT(D14
20)
If that formula returns 0 then your times aren't true Excel times.
How are you entering the time? Like this - 8:00 ? Another clue is the cell
alignment. Numbers (including time) will by default align to the right.
Text
will align to the left.
Try this.
Select an empty cell somewhere that hasn't been used before. It's default
format should be GENERAL.
Copy that empty cell: goto Edit>Copy
Select your range of times in D14
20
Goto Edit>Paste Special>ADD>OK
Did that do anything?
--
Biff
Microsoft Excel MVP
Hi Biff,Thanks for that, I'm thinking that you are correct that the
numbers
Hi Biff,
Thanks for that, I'm thinking that you are correct that the numbers are
not
True Excel times. The =count formula did return 0.
There are 4 columns in my worksheet. Date, Drop off Time, Pick Up Time,
Total Hours/Minutes.
So one row example would be:
Drop Off Pick Up Total Hours/Minutes
12:15 PM 5:15 PM 5:00
and the formula I used for that is =TEXT(C14-B14,"h:mm")
Your second suggestion of copying and pasting D14
20 into a blank spot on
the work sheet returned the #Value! error.
How do I enter "True Excel Times"? Thanks again for all your help
:
Re: How to Sum Time in Hours & Minutes
Ok, here's your problem:
The TEXT() function returns a TEXT string. Even though the result of that
formula may look like a time value it is not. It's a TEXT value.
So, use this as your formula:
=C14-B14
Format as h:mm
Or, to make it a little more robust:
=IF(COUNT(B14:C14)<2,"",C14-B14)
Then for the weekly/monthly total:
=SUM(D14
20)
Format as [h]:mm
--
Biff
Microsoft Excel MVP
Biff you are my hero! Thank you! Thank you! Thank you!
Biff you are my hero! Thank you! Thank you! Thank you!
I think what my problem is I just do not know what I am doing hahaha,
anyways
it works perfect now. Thanks again!
Teri
:
You're welcome. Thanks for the feedback!
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
How to Sum Time in Hours & Minutes
You are required to be a member to post replies. After logging in or
becoming a member, you will be redirected back to this page.
Submitted via EggHeadCafe - Software Developer Portal of Choice
Putting Twitter Realtime Search to Work
http://www.eggheadcafe.com/tutorial...24-c9960b55b669/putting-twitter-realtime.aspx