how to add hours and minutes in excel

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

  1. Guest

    Guest Guest

    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
    #1
    1. Advertisements

  2. Guest

    JE McGimpsey Guest

    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
    #2
    1. Advertisements

  3. Guest

    Bob Phillips Guest

    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(D2:D26-C2:C26)

    --

    HTH

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


    "M K Rao" <M K > wrote in message
    news:D...
    > 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
    #3
  4. Guest

    Guest Guest

    "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
    #4
    1. Advertisements

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.
Similar Threads
  1. Don Guillett
    Replies:
    0
    Views:
    308
    Don Guillett
    Aug 30, 2004
  2. Guest

    Hours and Minutes in Excell working with 5 digits hhhhh:mm

    Guest, Sep 29, 2004, in forum: Microsoft Excel New Users
    Replies:
    3
    Views:
    214
    Peo Sjoblom
    Sep 30, 2004
  3. Guest

    how to add hours and show actual hours not decimal numbers

    Guest, Aug 21, 2006, in forum: Microsoft Excel New Users
    Replies:
    2
    Views:
    258
    SteveW
    Aug 21, 2006
  4. Dave Gerecke

    How to add hours and minutes?

    Dave Gerecke, Jan 20, 2008, in forum: Microsoft Excel New Users
    Replies:
    5
    Views:
    327
    Dave Gerecke
    Jan 24, 2008
  5. wowo35

    converting days, hours and minutes to minutes

    wowo35, Jun 15, 2008, in forum: Microsoft Excel New Users
    Replies:
    1
    Views:
    183
    T. Valko
    Jun 15, 2008
Loading...

Share This Page