# how to add hours and minutes in excel

=?Utf-8?B?TSBLIFJhbw==?=
Guest
Posts: n/a

 24th Aug 2005
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

JE McGimpsey
Guest
Posts: n/a

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

=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 <(E-Mail Removed)>,
"M K Rao" <M K http://www.pcreview.co.uk/forums/(E-Mail Removed)> 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.

Bob Phillips
Guest
Posts: n/a

 24th Aug 2005
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 (E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> 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

=?Utf-8?B?TSBLIFJhbw==?=
Guest
Posts: n/a

 26th Aug 2005

"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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post StargateFan Microsoft Excel Misc 8 7th Jan 2006 08:35 PM =?Utf-8?B?V2Vz?= Microsoft Excel Worksheet Functions 2 2nd Mar 2005 12:33 AM Mark Microsoft Excel Worksheet Functions 1 22nd Sep 2004 08:49 AM Chinny Microsoft Excel Misc 3 19th Apr 2004 06:24 PM Donna Grant Microsoft Excel Misc 6 22nd Jan 2004 03:16 PM

Features