I am trying to figure out how to get the the total of days in a month like for every monday to saturday or tuesday to sunday then multiply by working hours. It depends on the user if what they like to input in cell.

If UCase(val) Like "*TO*" Then

Dim numStringTo As Integer
Dim strToDays() As String
Dim wordToCount As Long

numStringTo = 3

strToDays = VBA.Split(val, " ")
wordToCount = UBound(strToDays)
whEveryDay = ThisWorkbook.Sheets("Input").Cells(X, 4).Value
whEveryDay = whEveryDay * Weekday(nb_days, 6)
Debug.Print "Every = " & whEveryDay

End If

I need to get the total of days in a month and multiply by working hours. As of now we are in January 2023 and the pattern for January is 2-7,9-14,16-21,23-28,30-31 and the patter for November 2022 is 1-5,8-12,15-19,22-26,29-30.

For example:

Example of calculation:

Days * Working hours

And I need the calculation dynamically like for example if I change the cell of "every Monday to Saturday" to "every Wednesday to Monday" so, the count of days in a month will be also dynamically.

Thanks in advance,

James

CodePudding user response：

You could achieve that with a formula as well (Excel 365) and the following setting:

=LET(monthDays,SEQUENCE(EDATE(A2,1)-A2,1,A2),
weekdays,FILTER(SEQUENCE(1,7),B2:H2<>""),
workingHours,I2,
workedDays,FILTER(monthDays,ISNUMBER(MATCH(WEEKDAY(monthDays,2),weekdays,0))),
COUNT(workedDays)*workingHours)

CodePudding user response：

Option Explicit

Sub datecalc()

    Dim s as string, m As Integer, s1 As String, s2 As String, d As String
    Dim dtNow, dt As Date, dtStart As Date, dtEnd As Date
    Dim n As Integer, ar, bCount As Boolean, msg As String
    
    's = "every Tuesday to Saturday"
    'dtNow = DateSerial(2022, 11, 1) ' nov 22
    
    s = "every Monday to Saturday"
    dtNow = Date ' current month
    
    m = Month(dtNow) ' current month
    dtStart = DateSerial(Year(dtNow), m, 1)
    dtEnd = DateAdd("m", 1, dtStart) - 1
    
    ar = Split(s, " ")
    s1 = Left(ar(1), 3)
    s2 = Left(ar(3), 3)
    
    For dt = dtStart To dtEnd
        d = Format(dt, "ddd")
        If d = s1 Then bCount = True
        If bCount Then
            n = n   1
            msg = msg & vbLf & n & " " & Format(dt, "ddd dd")
        End If
        If d = s2 Then bCount = False
    Next
    
    MsgBox s & " = " & n & " days in " & Format(dtNow, "mmm yyyy") & msg
  
End Sub