# how to add hours and minutes in excel

Discussion in 'Microsoft Excel New Users' started by Guest, Aug 24, 2005.

1. ### GuestGuest

I have caliculated the time worked for the day using the formula =TEXT(D2-C2,
"hh:mm"") -where D2 is the time work closed and C2 is the time work begun. I
got total time worked for the day in hours and minutes. I caliculated like
this for the whole month. Now here I am facing a big problem totalling all
the worked hours for the whole month. I used the formula =SUM(E2:E26)
where E2 is D2-C2 and so on. But no result. I formatted the cell where the
formula =SUM(E2:E26) as [hh]:mm as I have seen this formatting on these
pages.But to no use.Can any one can help me how to solve this problem step by
step since i am very new to excel. Just I have started learning Excel. Yet I
don't know abcd of Excel. I will be very thankful for the help.
With best wishes to youngers and bestbregards to elders
M.Koteswara Rao
From INDIA

Guest, Aug 24, 2005

2. ### JE McGimpseyGuest

The reason SUM() is returning 0 is that all your calculated times are
Text, which SUM() ignores.

Instead of

=TEXT(D2-C2,"hh:mm")

use

=D2 - C2

and format the cell as a time (Format/Cells/Time)

If your hours span midnight, you'll have to correct for the fact that XL
stores times as fractional days, so, say, 9:00 pm = 0.875 and 3:00 am =
0.125.

One way to do that is to use XL's coercion of TRUE/FALSE to 1/0:

=D2 - C2 + (D2<C2)

a more obscure, but equivalent way, is

=MOD(D2-C2, 1)

In article <>,
"M K Rao" <M K > wrote:

> I have caliculated the time worked for the day using the formula =TEXT(D2-C2,
> "hh:mm"") -where D2 is the time work closed and C2 is the time work begun. I
> got total time worked for the day in hours and minutes. I caliculated like
> this for the whole month. Now here I am facing a big problem totalling all
> the worked hours for the whole month. I used the formula =SUM(E2:E26)
> where E2 is D2-C2 and so on. But no result. I formatted the cell where the
> formula =SUM(E2:E26) as [hh]:mm as I have seen this formatting on these
> pages.But to no use.Can any one can help me how to solve this problem step by
> step since i am very new to excel. Just I have started learning Excel. Yet I
> don't know abcd of Excel.

JE McGimpsey, Aug 24, 2005

3. ### Bob PhillipsGuest

You are trying to sum text cells, so that is why you get no result.

Either change E2 to =D2-C2 and format as time, or in the totals cell, use
this formula which bypasses the text file, =SUMPRODUCT(D226-C2:C26)

--

HTH

RP
(remove nothere from the email address if mailing direct)

"M K Rao" <M K > wrote in message
news...
> I have caliculated the time worked for the day using the formula

=TEXT(D2-C2,
> "hh:mm"") -where D2 is the time work closed and C2 is the time work

begun. I
> got total time worked for the day in hours and minutes. I caliculated like
> this for the whole month. Now here I am facing a big problem totalling all
> the worked hours for the whole month. I used the formula =SUM(E2:E26)
> where E2 is D2-C2 and so on. But no result. I formatted the cell where the
> formula =SUM(E2:E26) as [hh]:mm as I have seen this formatting on these
> pages.But to no use.Can any one can help me how to solve this problem step

by
> step since i am very new to excel. Just I have started learning Excel. Yet

I
> don't know abcd of Excel. I will be very thankful for the help.
> With best wishes to youngers and bestbregards to elders
> M.Koteswara Rao
> From INDIA

Bob Phillips, Aug 24, 2005
4. ### GuestGuest

"M K Rao" wrote:

> I have caliculated the time worked for the day using the formula =TEXT(D2-C2,
> "hh:mm"") -where D2 is the time work closed and C2 is the time work begun. I
> got total time worked for the day in hours and minutes. I caliculated like
> this for the whole month. Now here I am facing a big problem totalling all
> the worked hours for the whole month. I used the formula =SUM(E2:E26)
> where E2 is D2-C2 and so on. But no result. I formatted the cell where the
> formula =SUM(E2:E26) as [hh]:mm as I have seen this formatting on these
> pages.But to no use.Can any one can help me how to solve this problem step by
> step since i am very new to excel. Just I have started learning Excel. Yet I
> don't know abcd of Excel. I will be very thankful for the help.
> With best wishes to youngers and bestbregards to elders
> M.Koteswara Rao
> From INDIA

Mr JE McGimpsey and Mr Bob Phillips
Many Many Thanks to both of you for your help in solving my problem.By
following your advices I solved my problem.Onceagain I thank both of you.
With best regards
M K Rao

Guest, Aug 26, 2005

## Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.