Results 1 to 13 of 13

Running Time Calculations - Separated by Day

Threaded View

  1. #1
    Registered User
    Join Date
    11-25-2015
    Location
    Snellville, GA
    MS-Off Ver
    2007 SP2
    Posts
    9

    Question Running Time Calculations - Separated by Day

    I have a spread sheet that we have been using for about 2 years now on a week by week basis - and it has work perfectly all that time.

    I have been asked to extend this sheet so that ALL WEEKS for ALL EMPLOYEES can be added into one sheet to allow for additional reports and values to be calculated for billing purposes. I just extended the item lines from 200 lines to 2500 lines using the FILL>>DOWN option so that the formulas through the remainder of the sheet.

    It was working beautifully until I had another day's data pasted in which had the same DAY (i.e. JAN 09, and then MAR 09) - It started adding the days together.

    At first I thought I found a bug in EXCEL, then figured out what it is doing.

    The two date/time calculation fields are LOCKED files (in fact any field which is calculated or has a formula is LOCKED.

    I have the formula in the CALL TOTAL which takes a start time and stop time and checks for crossing the midnight mark and correctly calculates the hours and minutes

    Column B is DATE, Column C is START TIME, Column D is STOP TIME, Column E has this formula:

    =IF(D4<C4,IF(D4=0,0,(D4-C4+(D4<C4))),(D4-C4))

    This is working perfectly.

    Then column I keeps a running total for the DAY, and knows when the day changes and has this formula:

    =IF(DAY(B4)<>DAY(B5),SUMPRODUCT((DAY($B$4:$B$60)=DAY(B4))*($E$4:$E$60)),"")

    This works perfectly until you have another SAME DAY of another month (for example Jan 01, and then Feb 01). Then this value changes on both ROWS and keeps adding as long as the DAY is the same (Mar 01, Apr 01, etc).

    I need this TOTAL TIME to still keep the value for multiple rows for the same DAY, but not add the other times - the next time the DAY is the same. I hope I am making sense. I am going to attach the file to this message. I will not LOCK (Protect the sheet) - to test it fully as it is used, it should be protected.

    I know I am just overlooking something silly, but I think I need some additional brain power.

    All visible cells to Column J and Row 200 are all that are used.

    I hope that someone can help me get this working and that I have made sense.

    Many Thanks,
    Curtis

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. running several calculations in microsoft access
    By freak11 in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 06-08-2014, 10:29 AM
  2. [SOLVED] Calculations running extremely slow, any suggestions
    By Dena in forum Excel General
    Replies: 1
    Last Post: 08-09-2013, 02:01 PM
  3. Dates/Time separated. Help please.
    By auswtz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-31-2013, 08:03 AM
  4. [SOLVED] Extract a time from a cell with date and time stamp and then perform calculations
    By Marcos Aristotelous in forum Excel General
    Replies: 3
    Last Post: 10-31-2012, 04:36 AM
  5. Running calculations by date
    By rtabaldi in forum Excel General
    Replies: 3
    Last Post: 06-01-2011, 02:22 AM
  6. Running balance calculations?
    By geng in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-16-2010, 04:48 AM
  7. [SOLVED] convert time imported as text to time format for calculations
    By batfish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-27-2005, 07:05 PM

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