+ Reply to Thread
Results 1 to 3 of 3

Formula to calculate time differences but not all cells have values?

  1. #1
    Registered User
    Join Date
    03-18-2013
    Location
    Raleigh
    MS-Off Ver
    Excel 2007
    Posts
    14

    Question Formula to calculate time differences but not all cells have values?

    Hi. I've tried searching for an answer to this but have come up empty handed. Perhaps someone can help.

    I want to calculate time difference between several values AND then be able to SUM those times (I want daily and monthly totals for time on level 1, time on level 2, and total). Unfortunately, I will have blanks in my sheet.

    When I use a conditional statement such as IF(ISBLANK(CELL) to calculate 1 total value, I cannot then use that cell in another formula for monthly sum without getting a #value or #### error.

    I am creating this spreadsheet so I am open to suggestions on how best to format it.

    Example of my data:
    1. Date/Time Level 1 On: 12/2/2013 18:54
    Date/Time Escalated to Level 2: blank/empty cell
    Date/Time Off: 12/2/2013 22:54

    2. Date/Time Level 1 On: 12/3/2013 12:50
    Date/Time Escalated to Level 2: 12/3/2013 13:36
    Date/Time Off: 12/3/2013 21:31

    3. Date/Time Level 1 On: 12/4/2013 16:27
    Date/Time Escalated to Level 2: 12/5/2013 02:29
    Date/Time Off: blank/empty cell

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Formula to calculate time differences but not all cells have values?

    If you are in the process of designing the sheet, it will be easier to process if you arrange the date/times in columns across for each record, i.e.:

    Level 1 ON ............|... Level 2 ON ............|... OFF
    12/2/2013 18:54 ...|... blank ....................|...12/2/2013 22:54
    12/3/2013 12:50 ...|... 12/3/2013 13:36 ...|...12/3/2013 21:31
    12/4/2013 16:27 ...|... 12/5/2013 02:29 ...|... blank

    and so on.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-18-2013
    Location
    Raleigh
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Formula to calculate time differences but not all cells have values?

    Yes, that is how it is currently arranged. The problem comes when attempting to calculate the times with the blank cells.

+ 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. Formating cells according to time differences
    By Fran Habbitts in forum Excel General
    Replies: 0
    Last Post: 09-26-2012, 04:39 AM
  2. Problem to calculate differences in cell values when columns change.
    By chrisjc19 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-09-2011, 11:03 AM
  3. formula to calculate time values
    By kjp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2006, 04:45 PM
  4. How to calculate Date & Time differences
    By robs in forum Excel General
    Replies: 8
    Last Post: 10-05-2005, 10:05 PM
  5. How to calculate Date & Time differences
    By robs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-04-2005, 11:22 AM

Tags for this Thread

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