QUESTION: I have a Table spreadsheet:
Header; A: Date, B:Unit, C:Room, D: Employee Name, E: Agency, F: Start Time, G:End Time,H: Hours, I:Break, J:Total hours, K:Total Mins.
I already have some simple formula
K: =J272 *60*24
Locale: English (US)
F & G: Time, 1:30 PM
H & J: Time, 13:30
We do business with 4 Agencies; MSN, MS, SOS, FH all except MSN charge $17.00/hr.
MSN between the hours of 7am – 7 pm charge $15.00/hr.
and between 7pm – 7am charge $ 17.00/hr.
and in column E: Agency I input MSN, MS, FH, and SOS.
Time I use is in 15 minutes increments, so I only input 7:00 pm, 7:15 pm, 7:30 pm, or 7:45 pm...
I am trying to find out the amount we should be invoiced for each employee for each day.
Well I hope to hear from you soon.
ANSWER: Hello Peggy,
I have one question - When you are entering the start and end times in the columns F & G, do you enter only the time or do you enter the entire date, month, year, hours and minutes.
I'm assuming if you have this data dumped form some sort of time keeping software, you might be able to get the start and end times along with date. Start and end times with date will make it easier to manage the number of hours. (I know there's a date column but it might differ in case of night shifts or if someone works over a 2 day period)
Also, would you be comfortable with using Macros.
---------- FOLLOW-UP ----------
QUESTION: Hello Gulshan,
Thank you for your quick response. No I only enter the times and the times are in 15 minutes increments. Time I use is in 15 minutes increments, so I only input 7:00 PM, 7:15 PM, 7:30 PM, or 7:45 PM...
so for start time i will enter 7:00 PM and end time 7:00 am.
I only enter the date once. If you like I can e-mail you a sample spreadsheet.
There is no software we use, just my spread sheet. They are not allow to work more that 16 hours.
so for msn if they clock in between 7a -7p the bill us $15.00/hr but if they clock in between 7p-7a then they bill us 17.00/hr.
All others bill us 17.00/hour no matter what time. I am not familiar with macros.
Thank you for your time
I've been working on this one for a couple of days now and I have a few problems with the way your data is currently handled:
1. Start time and end time do not have a date. This is ok if you were doing a straight calculation, but the moment you use MSN style billing, it becomes an issue. Eg. start time could be before 07:00, between 07:00 & 19:00 or after 19:00 on either the current day or on the previous day, similarly end time could also be before 07:00, between 07:00 & 19:00 or after 19:00 on either the current day or on the previous day. This gives potentially 36 different combinations which need to be handled ((3*2)*(3*2)). Excel can handle only upto 7 if conditions looped within a single formula.
2. While calculating the MSN charges you will need to track the break time details as well, whether the break was during the day time or during the night time or the break could be from 07:30 to 08:30.
You might need to make changes to the file format like - include date into the start and end times, split the number of hours into day and night sessions of 7-7, track the break timings (or create an assumption that breaks are always during day / night) etc.
If you could define the entire logic in a fool proof manner, I might be able to help you out.