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

bebo021999,

I think you may have gotten it. I will have to play with it a little more.

Like I said - I only needed another set of eyes on it. I will start migrating the weekly files to the master and let you know how it goes.

Image1.jpg

Curtis

bebo021999,

You are a genius. Your solution worked like a champ.

So I wonder....can another column be added which will do the same thing, but total for the WEEK?

This sheet is used to track weekly After Hours support calls and I can see a need to do that as well.

I will play with it is well on my end.

Then I get to create a sheet to calculate total hours based on the ACCOUNT. That one is gonna be fun.

Curtis

bebo021999,

Sorry for the delay in response. It was the weekend (and a holiday here in the states) - was not looking at this until today.

I am going to attach the file here with the new column added for TOTALED WEEKLY TIME. This is the one where I would like to have a total for the week. Weeks are MON-SUN for the purpose of this sheet.

I am also having another issue. I am trying to name the 2nd sheet to ACCOUNTS so that I can have a drop-down list in the ACCOUNTS COLUMN but I am getting a strange error. I have made sure that there are no references to another sheet/workbook, no named ranges, nothing that I can find to give reason for the error.

Error_Accounts.jpg

Also I have a CONDITIONAL FORMAT in the ANALYST column to change the color of the cell based on the name in there. Is there a way to FORCE that column to UPPER CASE if someone types in a lower-case or mixed case name?

I appreciate your help with this....I think I have been away from the EXCEL world too long. Some of it is coming back, but in pieces.

Updated_FORUM POSTED_WEEKENDING_mm-dd-yyyy_AFTER HOURS TIME SHEET.xlsx


Thanks,
Curtis

You have a hidden sheet called "ACCOUNTS"!

Well that explains it.... I did not HIDE IT.
I had someone else testing the sheet ... and they must have hidden it.

One issue fixed.


Thanks,

Curtis

bebo021999,

Did you get a chance to look at the new file posted. I resolved the ACCOUNTS issue - the other person I had testing the file had hidden the tab at the bottom.

I have played with the totaling by week and I am not having much success.

Thanks,

Curtis

bebo021999,

Have you abandoned me? I keep trying to get one column to TOTAL by DAY and another column to TOTAL by WEEK. It looks like it is working but when the week changes - it clears out the previous week.

Also - much like I am creating Daily totals...do you think on another tab (as you see I have various accounts on page) to have a running total for each account?

Thanks, and as they say on your side of the pond....Joyeux Noël et Bonne Année

Curtis

bebo021999,

Sorry. Here is an updated file with data in it.

So what I am looking to do is A) Utilize the TOTAL BY WEEK column to create a total BY WEEK (Mon-Sun) as well as BY DAY. B) Create some kind of record of # of calls per account and a total time per account.


Updated_FORUM POSTED_WEEKENDING_mm-dd-yyyy_AFTER HOURS TIME SHEET.xlsx

Much Thanks,
Curtis

bebo021999,

HAPPY HOLIDAYS. Were you able to figure out a way to get the WEEKLY CALCULATION?

I have not seen a posting from after your request for the file with data.


Thanks,

Curtis