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
Bookmarks