How to Sum Time in Hours & Minutes

[Teri]

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(D1420) 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

 

[T. Valko]

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

 

[Teri]

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

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 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(D1420) 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

 

[T. Valko]

Any other ideas?

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 D1420. If every cell has a true Excel
time in it then this formula will return 7:

=COUNT(D1420)

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 D1420
Goto Edit>Paste Special>ADD>OK

Did that do anything?


--
Biff
Microsoft Excel MVP

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

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 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(D1420) 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

 

[Teri]

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 D1420 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

Any other ideas?

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 D1420. If every cell has a true Excel
time in it then this formula will return 7:

=COUNT(D1420)

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 D1420
Goto Edit>Paste Special>ADD>OK

Did that do anything?


--
Biff
Microsoft Excel MVP

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

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 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(D1420) 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

 

[T. Valko]

Ok, here's your problem:

the formula I used for that is =TEXT(C14-B14,"h:mm")

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(D1420)

Format as [h]:mm


--
Biff
Microsoft Excel MVP

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 D1420 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

Any other ideas?

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 D1420. If every cell has a true Excel
time in it then this formula will return 7:

=COUNT(D1420)

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 D1420
Goto Edit>Paste Special>ADD>OK

Did that do anything?


--
Biff
Microsoft Excel MVP

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

:

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 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(D1420) 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

 

[Teri]

Biff you are my hero! Thank you! Thank you! Thank you!

I think what my problem is I just don't know what I am doing hahaha, anyways
it works perfect now. Thanks again!

Teri

Ok, here's your problem:

the formula I used for that is =TEXT(C14-B14,"h:mm")

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(D1420)

Format as [h]:mm


--
Biff
Microsoft Excel MVP

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 D1420 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

Any other ideas?

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 D1420. If every cell has a true Excel
time in it then this formula will return 7:

=COUNT(D1420)

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 D1420
Goto Edit>Paste Special>ADD>OK

Did that do anything?


--
Biff
Microsoft Excel MVP


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

:

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 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(D1420) 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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Biff you are my hero! Thank you! Thank you! Thank you!

I think what my problem is I just don't know what I am doing hahaha,
anyways
it works perfect now. Thanks again!

Teri

Ok, here's your problem:

the formula I used for that is =TEXT(C14-B14,"h:mm")

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(D1420)

Format as [h]:mm


--
Biff
Microsoft Excel MVP

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 D1420 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

:

Any other ideas?

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 D1420. If every cell has a true
Excel
time in it then this formula will return 7:

=COUNT(D1420)

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 D1420
Goto Edit>Paste Special>ADD>OK

Did that do anything?


--
Biff
Microsoft Excel MVP


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

:

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 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(D1420) 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

 

[Mike Burrell]

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]:m

The brackets keep the hours from rolling over into days

8:0
8:0
8:0

=SUM(A1:A3) formatted as h:mm = 0:00. Formatted as [h]:mm = 24:0


--
Bif
Microsoft Excel MV


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(D1420) 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]:m

The brackets keep the hours from rolling over into days

8:0
8:0
8:0

=SUM(A1:A3) formatted as h:mm = 0:00. Formatted as [h]:mm = 24:0


--
Bif
Microsoft Excel MV


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 n
change. I tried added it to the equation and it gave me the value error
Any other ideas

Ter

:

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 D1420. If every cell has a true Excel
time in it then this formula will return 7

=COUNT(D1420

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>Cop
Select your range of times in D142
Goto Edit>Paste Special>ADD>O

Did that do anything

--
Bif
Microsoft Excel MV


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/Minute
12:15 PM 5:15 PM 5:0

and the formula I used for that is =TEXT(C14-B14,"h:mm"

Your second suggestion of copying and pasting D1420 into a blank spot on
the work sheet returned the #Value! error

How do I enter "True Excel Times"? Thanks again for all your hel

:

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(D1420)

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

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

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(D1420) 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 D1420. If every cell has a true Excel
time in it then this formula will return 7:

=COUNT(D1420)

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 D1420
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 D1420 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(D1420)

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