Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
M K Rao
 
Posts: n/a
Default how to add hours and minutes in excel

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
  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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.

  #4   Report Post  
M K Rao
 
Posts: n/a
Default



"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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding hours and minutes Patrick Excel Worksheet Functions 16 June 18th 08 09:24 PM
Help for a newbie trying to add hours and minutes :) Bugjam1999 Excel Discussion (Misc queries) 8 August 11th 05 05:21 PM
Can I use Excel to add hours and minutes like keeping a dive log? BobL Excel Discussion (Misc queries) 1 August 3rd 05 01:40 PM
excel [HHHHH]:mm add subtract hours mins inc lnegative mexico Excel Worksheet Functions 0 July 5th 05 05:45 PM
Convert hours and minutes in time format into fractions of hours.. Akern Excel Worksheet Functions 4 April 21st 05 02:56 PM


All times are GMT +1. The time now is 11:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"