Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: how in the world can I

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    do this? I'm new to this board as well as excel. My problem is I'm trying to figure out a forumla that will give me a total time (hours & mins) but also use the date. such as, I have a job that starts on 4/8 1943 and ends on 4/9 2030. the forumla I have now only shows 47 mins. but such be 24hr 47 mins.
    can anyone help me? TIA

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    just a quick one if you change the dates around 8/4 and 9/4 then subtract the differece and format the cell to custom d-hhmm it will give you 1-47 any help?

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    start 8/04/2002 19:43
    stop 9/04/2002 20:30
    1- 00:47
    assuming 8/4 is in c2 and 9/4 is in c3
    =c3-c2
    then go into format cells
    custom and enter d-hh:mm

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Brettvba, Thanks for the quick reply. I'll give you answer a shoot. However I was sorta thinking along the lines as the following setup.
    B1= start Date
    C1= start time
    D1= end date
    E1= end time

    the current formula I'm using only figures out the total for C1 and E1
    =(2400-C1)+(2400+E1)-(2400)

    But I need to add the other two columns and get them into the formula. Hope you can help me?? TIA

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok how about assuming your cells are in where you wanted b2 c2 etc

    =SUM(D2-B2)+SUM(E2-C2)
    and format the cell to
    d" Day/s "hh:mm

    that help?

    [ This Message was edited by: brettvba on 2002-04-18 19:14 ]

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 18:16, confused wrote:
    do this? I'm new to this board as well as excel. My problem is I'm trying to figure out a forumla that will give me a total time (hours & mins) but also use the date. such as, I have a job that starts on 4/8 1943 and ends on 4/9 2030. the forumla I have now only shows 47 mins. but such be 24hr 47 mins.
    can anyone help me? TIA
    Hi Friend:
    No need to be confused. Let us say your start time 4/8 19:43 is in cell A2, and end time 4/9 20:30 is in cell B2, then in cell C2 put the formula:
    =B2-A2 and format C2 as CUSTOM FORMAT [h]:mm:ss
    and you wii see the result as 24:47:00

    HTH

    Please post back if it works for you ... otherwise explain a little further and let us take it from there!
    [/quote]

    _________________
    Yogi Anand
    Edit: Deleted reference to inactive web site from signature line

    [ This Message was edited by: Yogi Anand on 2003-01-19 18:27 ]

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I wish I could say that helped, but sorry it doesn't. Thanks for trying to help tho. What I really need is a formula that would add/subtact the two date columns together and add/subtact the two time columns and give me the total. my date columns are in dd-mm-yyyy format and my time is in hh:mm:ss format.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sorry have i misunderstood are you after days and hours/minutes or just the total minutes or just hours/minutes if its days and hours/minutes my example works well

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 19:03, confused wrote:
    Brettvba, Thanks for the quick reply. I'll give you answer a shoot. However I was sorta thinking along the lines as the following setup.
    B1= start Date
    C1= start time
    D1= end date
    E1= end time

    the current formula I'm using only figures out the total for C1 and E1
    =(2400-C1)+(2400+E1)-(2400)

    But I need to add the other two columns and get them into the formula. Hope you can help me?? TIA
    Use the formula

    =D1-B1+E1-C1 ... CUSTOM format it as [h]:mm:ss to get the result 24:47:00

    HTH

    Yogi Anand


  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi your the man .... Thanks a whole lot, I tried it out and was blown out of my chair ..lol. Again Thanks for the help..

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •