returning values based on time frame

[jhamm2018]

Good afternoon,

I am currently working on building an employee hours template, but am getting stuck with getting the data to pull through correctly.

The problem:
Getting an employee hours to be broken out by day-part. How many hours is an employee working/available per the morning (open to 11am)/day(11am - 3pm)/evening (3pm to close). With Weekends being calculated independently.

Currently I have the spreadsheet (sheet 1320 Original) broken out by day and day-part. Then calculating how many hours a given employee is scheduled (columns AU - AX) and then finally being calculated on (columns AZ - BC). But I am looking to simplify/slimming the spreadsheet (sheet 1320 - WIP) so that each day of the week has a start and end time.

Is it possible in Excel to do an if/sum statement based on a time-frame, and then be able to calculate how many hours are scheduled for a given day-part? So if someone is scheduled between 2pm and 6pm. One hour would populate in the Day column Y and 3 hours would populate under the Evenings column Z.

Another inquirer is to see if it's possible to have a cap set at a predetermined number (40 hours) and flag the user if they are over for a given employee?

I apologize if this has been solved previously. Everything i have researched and tried has not worked out how I through it would.

Thank you
James Hammock

[JeteMc]

Is the attached file the same as the one that you posted in the Timesheet Issues thread?
If not, and if you wish to continue with this thread, please attach that file to this thread and tell us specifically what you need help with.
Let us know if you have any questions.

[jhamm2018]

Hi JeteMc,

It is the same one as in the Timesheet Issue thread. But I removed that post to comply with the the forum rules. Thank you for the reply.

[JeteMc]

My suggestion would be to use the 1320 Original sheet as the computational engine with the 1320 WIP sheet used for input and output/display.
I only filled in the Wednesday and Morning sections of the 1320 Original sheet. Apply the same formulas to the other days/times of day (changing the reference cells of course).
1. The Mornings Start column uses: =IF(AND(' 1320 - WIP'!$E4<>"",' 1320 - WIP'!$E4<F$1),' 1320 - WIP'!$E4,"")
2. The Mornings End column uses: =IF(E5="","",IF(' 1320 - WIP'!$F4>F$1,F$1,' 1320 - WIP'!$F4))
3. The Days Start column uses: =IF(AND(' 1320 - WIP'!$E4<>"",' 1320 - WIP'!$E4<H$1,' 1320 - WIP'!$E4>=F$1),' 1320 - WIP'!$E4,IF(AND(E5<>"",' 1320 - WIP'!$F4<>"",' 1320 - WIP'!$F4>F$1),F$1,""))
4. The Days End column uses: =IF(G5="","",IF(' 1320 - WIP'!$F4>H$1,H$1,' 1320 - WIP'!$F4))
5. The Evenings Start column uses: =IF(AND(' 1320 - WIP'!$E4<>"",' 1320 - WIP'!$E4>=H$1),' 1320 - WIP'!$E4,IF(AND(G5<>"",' 1320 - WIP'!$F4<>"",' 1320 - WIP'!$F4>H$1),H$1,""))
6. The Evenings End column uses: =IF(I5="","",' 1320 - WIP'!$F4)
Note that cell F1 contains the value 11:00 AM and cell H1 contains the value 3:00 PM
On the 1320 Origin sheet the Mornings column uses: =IF(E5="",0,F5-E5)+IF(K5="",0,L5-K5)+IF(Q5="",0,R5-Q5)+IF(AI5="",0,AJ5-AI5)+IF(AO5="",0,AP5-AO5)
On the 1320 WIP sheet the Mornings column (S) uses: ='1320 Original'!AU5
I will leave the Thursday:Tuesday sections as well as Days and Evenings in your capable hands.
Let us know if you have any questions.

[menem]

(Sorry about my English)

I've apply formula in range S1:V2 and S4:V6 ( include change number format )
I hope it's OK.

Regards.

[jhamm2018]

JeteMc and Menem thank you so much for both of these options. I will use both to see what works best for my application. But this gets me to where I needed to be.

[JeteMc]

Have a feeling the menem's solution is going to work best, simpler and doesn't require the use of the 1320 - Original sheet. (I wish I had thought of it myself to be honest).
@menem. Nice formula solution. In the future it would be helpful to others whom may be looking for similar solutions, if you would include the formulas in the post.
I hope you both have a blessed day.

[menem]

(Sorry about my English).

JeteMc, I'll try my best (with my poor English) >_<".

Concept of calculation is base on 2 type of data,
1:WorkDay
2:Holiday

WorkDay have 3 time interval [ >0.00-11:00 , >11:00-15:00 , >15:00-EndOfDay ]
So I use cells to store these time for begin (row1) and ending (row2) in column
S, T and U as for WorkDay

Then use formula that refered from workday (Mon/Tue/Wed/Thu/Fri)
by use start/stop time compare to begin/ending time of each interval.

S4
=IF(AND($E4<=S$2,$F4>=S$1),IF($F4>S$2,S$2,$F4)-IF($E4<S$1,S$1,$E4),0)
+IF(AND($G4<=S$2,$H4>=S$1),IF($H4>S$2,S$2,$H4)-IF($G4<S$1,S$1,$G4),0)
+IF(AND($I4<=S$2,$J4>=S$1),IF($J4>S$2,S$2,$J4)-IF($I4<S$1,S$1,$I4),0)
+IF(AND($O4<=S$2,$P4>=S$1),IF($P4>S$2,S$2,$P4)-IF($O4<S$1,S$1,$O4),0)
+IF(AND($Q4<=S$2,$R4>=S$1),IF($R4>S$2,S$2,$R4)-IF($Q4<S$1,S$1,$Q4),0)


Weekday is more easier because it have only 1 interval.
And again refered from weekday (Sat/Sun)

V4
=IF(AND($K4<=V$2,$L4>=V$1),IF($L4>V$2,V$2,$L4)-IF($K4<V$1,V$1,$K4),0)
+IF(AND($M4<=V$2,$N4>=V$1),IF($N4>V$2,V$2,$N4)-IF($M4<V$1,V$1,$M4),0)

All value are in Excel standard time format (1=1 Day) so, it can be calculate
as a normal number with others formula (column X to AA)

Regards.