+ Reply to Thread
Results 1 to 2 of 2

Calculate time difference for planned v actual times

  1. #1
    Registered User
    Join Date
    06-30-2014
    Location
    edinburgh
    MS-Off Ver
    2011
    Posts
    1

    Calculate time difference for planned v actual times

    i have a spreadsheet which i want to calculate the variance between planned v actual times, i have following columns
    Planned start, actual start, planned finish, actual finish, total hours, total planned hours and hours variance
    the problem starts when i have a finish time after midnight, keeps returning negative total hours eg

    planned start 13:00
    Actual start 13:00
    Planned finish 00:30
    Actual finish 00:30
    Total planned hours is returning -12:30, same with actual hours. I have excel using the 1904 date system but cant get this to work, anyone have any ideas?

    Lawry

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Calculate time difference for planned v actual times

    Time is stored as a floating-point number between 0 and 1.

    So, something like thirty minutes past midnight would be something like 0.02 and 13:00 would be about 0.54
    0.54 - 0.02 = -0.52 => negative 12 1/2 hours

    Two solutions:
    1) Add date information
    The reason time is stored as a floating point between 0 and 1 is becuase dates are stored as integers from day 1 at the start of the calendar to now; that way date+time can be stored as one big ol' number in the cell.
    1000.02 - 999.54 = the correct output

    2) Add assumptions
    =IF(start_time <= end_time, end_time - start_time, 24_hours - end_time + start_time)
    This will assume that if the end_time happens "before" the start_time, it's actually the next day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calendar: Calculate Taken vs Planned time
    By Smom in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-26-2011, 11:08 AM
  2. Traffic Light for Hours / Days actual vs planned
    By JPD in forum Excel General
    Replies: 4
    Last Post: 06-08-2011, 10:21 AM
  3. Planned vs. Actual Gantt
    By mycon73 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-22-2011, 01:10 AM
  4. Excel 2007 : Gathering Planned and Actual hours
    By Kburtt in forum Excel General
    Replies: 0
    Last Post: 02-28-2011, 10:42 PM
  5. Conditional Formating planned VS actual date
    By rhatala@stny.rr.com in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-02-2005, 04:06 PM

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