Sumifs Help - Payroll Planning

[acctmn]

See the table below, I'm looking to calculate available hours in Col D.

the SUMIF statements for the FT and PT statuses are straightforward. Looking for help on the date conditional in regards to 'Seas' Status.

If the status is 'Seas' I want their Hrs Avl added to the total Available hours based on their Avl Date and End Date.

If I need to convert the Avl/End Date in Col I and J to match the dates in A and C to make the formula simpler that can be done.

First time post, sorry if this isn't how it's supposed to work or is missing info.



[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH][/TH]
[TH][/TH]
[TH][/TH]
[TH]Available Hours[/TH]
[TH][/TH]
[TH]Name[/TH]
[TH]Status[/TH]
[TH]Av. Hrs[/TH]
[TH]Avl Date[/TH]
[TH]End Date[/TH]
[/TR]
[TR]
[TD]12/31/2018[/TD]
[TD]-[/TD]
[TD]1/6/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/7/2019[/TD]
[TD]-[/TD]
[TD]1/13/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]Mike[/TD]
[TD]PT[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/14/2019[/TD]
[TD]-[/TD]
[TD]1/20/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]Steve[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/21/2019[/TD]
[TD]-[/TD]
[TD]1/27/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]Bill[/TD]
[TD]Seas[/TD]
[TD]40[/TD]
[TD]1/5/2019[/TD]
[TD]2/5/2019[/TD]
[/TR]
[TR]
[TD]1/28/2019[/TD]
[TD]-[/TD]
[TD]2/3/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD]Seas[/TD]
[TD]30[/TD]
[TD]2/1/2019[/TD]
[TD]3/1/2019[/TD]
[/TR]
[TR]
[TD]2/4/2019[/TD]
[TD]-[/TD]
[TD]2/10/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]Ashley[/TD]
[TD]PT[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/11/2019[/TD]
[TD]-[/TD]
[TD]2/17/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/18/2019[/TD]
[TD]-[/TD]
[TD]2/24/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/25/2019[/TD]
[TD]-[/TD]
[TD]3/3/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/4/2019[/TD]
[TD]-[/TD]
[TD]3/10/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.
This is one method :

Paste this macro into a Routine Module :

Option Explicit


Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
 
 Dim WholeWeeks As Variant
 Dim DateCnt As Variant
 Dim EndDays As Integer
 
 On Error GoTo Err_Work_Days
 
 BegDate = DateValue(BegDate)
 EndDate = DateValue(EndDate)
 WholeWeeks = DateDiff("w", BegDate, EndDate)
 DateCnt = DateAdd("ww", WholeWeeks, BegDate)
 EndDays = 0
 
 Do While DateCnt <= EndDate
 If Format(DateCnt, "ddd") <> "Sun" And _
 Format(DateCnt, "ddd") <> "Sat" Then
 EndDays = EndDays + 1
 End If
 DateCnt = DateAdd("d", 1, DateCnt)
 Loop
 
 Work_Days = WholeWeeks * 5 + EndDays
 
Exit Function
 
Err_Work_Days:
 
 ' If either BegDate or EndDate is Null, return a zero
 ' to indicate that no workdays passed between the two dates.
 
 If Err.Number = 94 Then
 Work_Days = 0
 Exit Function
 Else
' If some other error occurs, provide a message.
 MsgBox "Error " & Err.Number & ": " & Err.Description
 End If
 
End Function

Format COL D as NUMBER / 2 places decimal

Paste this formula in D3 and drag down : =IF(E3<>"",(H3/8)*E3,"")

Paste this formula in E3 and drag down : =IF(G3="Seas",Work_Days(I3,J3),"")



Download example workbook : https://www.amazon.com/clouddrive/share/ve8xGrV8kUgHcBUYAf3NFMhW903qLSELoBNMZSJNYG7

 

[Logit]

.
Also keep in mind the solution considers a work day as Mon - Fri and does not account for holidays during those days.

A solution to including holidays can be seen here : https://exceljet.net/formula/get-workdays-between-dates

using this formula (and removing the macro FUNCTION) : =Networkdays(start_date,end_date,holidays)