How to get the total of every monday to saturday or tuesday to sunday in a month dynamically

0 votes

I'm attempting to find out how to multiply the number of working hours by the number of days in a month, such as every Monday through Saturday or Tuesday through Sunday. What the user chooses to enter the cell relies on them. The CALCULATION, however, is based on the date that the user inputs in a textbox or 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:

Days Date Working Hours
every Monday to Saturday 2-7,9-14,16-21,23-28,30-31 1.2
every Tuesday to Saturday 1-5,8-12,15-19,22-26,29-30 0.5

Example of calculation:

Days * Working hours

And I need the computation to be dynamic, so for instance, the number of days in a month will change if I change the cell from "every Monday to Saturday" to "every Wednesday to Monday."

Jan 29, 2023 in Others by Kithuzzz
• 38,000 points
636 views

1 answer to this question.

0 votes


Try this:

Option Explicit
Sub demo()
    Dim lastrow As Long, r As Long, s As String, dt As Date
    s = InputBox("Input Date")
    If IsDate(s) Then
        dt = CDate(s)
    Else
        MsgBox s & " not a date", vbCritical
        Exit Sub
    End If

    With ThisWorkbook.Sheets("Input")
        lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
        For r = 2 To lastrow
            s = .Cells(r, "B").Value
            If UCase(s) Like "*TO*" Then
               ' total days in column E
               .Cells(r, "E") = mydatecalc(dt, s)
               ' hrs per day * days
               .Cells(r, "F").FormulaR1C1 = "=RC[-2]*RC[-1]"
            End If
        Next
    End With
    MsgBox "Calc done for " & Format(dt, "mmm yyyy")
End Sub

Function mydatecalc(dtNow As Date, s As String) As Long

    Dim m As Integer, s1 As String, s2 As String, d As String
    Dim dt As Date, dtStart As Date, dtEnd As Date
    Dim n As Integer, ar, bCount As Boolean, msg As String
         
    m = Month(dtNow) ' input 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
    mydatecalc = n
End Function
answered Jan 29, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
2 answers

How to get the URL of the current tab in Google Chrome?

Its so simple.... If you want to ...READ MORE

answered Aug 12, 2020 in Others by Steve
• 200 points
3,610 views
0 votes
1 answer

How to retrieve the value of a text field in Flutter App?

Hi@akhtar, In your TextField, you can call one ...READ MORE

answered Sep 3, 2020 in Others by MD
• 95,460 points
1,819 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
797 views
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,600 points
2,361 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,259 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,697 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
974 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,089 views
0 votes
1 answer

How to programmatically get the values of a spilled Excel range in VBA?

By using the Text property, I was ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,600 points
1,292 views
0 votes
1 answer

How to get rid of a #value error in Excel?

Changing the format to "Number" doesn't actually ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,600 points
809 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP