+ Reply to Thread
Results 1 to 5 of 5

Calculating time difference between PM and AM

  1. #1
    Registered User
    Join Date
    10-01-2011
    Location
    West Midlands, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Calculating time difference between PM and AM

    Hi guys,
    I would really appreciate if someone could help me with a problem I am having.

    Start time is in column D
    Finish time is in column E
    Rest time in column G.

    I need to calculate in G the total REST hours between E6 and D7, (onwards), but the start time is PM, and the finish time is AM.

    Many thanks in advance, and if anyone could recommend a TIME related tips/tutorial formula link, again I would greatly appreciate your help.

    Regards
    Paul
    Last edited by Paul64; 10-04-2011 at 10:59 AM.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Calculating time difference between PM and AM

    I would suggest http://www.cpearson.com/excel/datetime.htm#AddingDates for your tutorial

    For the difference try
    Please Login or Register  to view this content.
    and format as needed

  3. #3
    Registered User
    Join Date
    10-01-2011
    Location
    West Midlands, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Calculating time difference between PM and AM

    Thanks for the quick response Arthurbr, I tried that, or at least I think I have, being an Excel noob I might have got this wrong but I entered:-
    =MOD(E6-D7,-1) and it reads #########dates and times that are negative or too large .........

    thanks for that link too, just had a quick browse through and I'm sure that will help me with other problems.
    Last edited by Paul64; 10-04-2011 at 10:38 AM.

  4. #4
    Registered User
    Join Date
    10-01-2011
    Location
    West Midlands, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Calculating time difference between PM and AM

    Solved through the link supplied by Arthurbr, many thanks.
    Formula is =IF(E5>D6,D6+1-E5,D6-E5)

  5. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Calculating time difference between PM and AM

    Paul,
    there is a typo in your MOD formula, that is why it does not work

    The MOD function ends with ,1 not ,-1

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1