CODEWITHSUNDEEP
excelexcel-formulaschedule
arnold-c
arnold-c

Reputation: 357

Dynamic Work Schedule Based on Day of Week

I'm trying to create a work schedule that adjusts the start and end date of a task (in this case a 'lesson') based on whether it is a weekday and/or a weekend. I have assigned a value in terms of complete days to each 'lesson', based on the day of the week. My hope is that if a lesson takes 2 days to complete during the week, and the range 'start.date:start.date + 2' doesn't contain a weekend day, then the end date would be 'start.date + 2' (e.g. Monday + 2). Equally, if that same lesson would take 1 day to complete on the weekend, and the range 'start.date:start.date + 1' doesn't contain a weekday, then the end date would be 'start.date + 1' (e.g. Saturday + 1).

However, the tricky part is when that range contains a mix of weekday and weekend. In that situation I'd like it to switch between the two lengths. For example, if all lessons take 2 days during the week and 1 day on the weekend, if: 

start.date(1) = beginning of Friday, end.date(1) = halfway through Saturday (1 weekday + 0.5 weekend).
start.date(2) = halfway through Saturday, end.date(2) = halfway through Sunday.
start.date(3) = halfway through Sunday, end.date(3) = end of Monday (0.5 weekend + 1 weekday).

I've attached a spreadsheet along with images showing the formulas that I currently have. It works OK until the end date in cell H11. It should read Tuesday (as J11 should = 1, and K11 should = 0.5)

https://1drv.ms/x/s!ApoCMYBhswHzhttuLQmKNVw7G6pHSw

If this would be better suited to Python or R, or even VBA, then I'm more than happy to hear suggestions for those (also including relevant things to read so that I can start writing the necessary code), but I just don't have the required knowledge in them to make a decent start at the moment.

Thanks for your help.

enter image description here

enter image description here

Upvotes: 0

Views: 225

Answers (1)

usmanhaq
usmanhaq

Reputation: 1577

You have to insert this macro into your workbook, I have mentioned the steps but if you find it difficult you can let me know.

  • Click Alt+F11 in your excel window
  • The VBA editor will open in a new window, in this window there is a left side pane named as project window
  • Right click this workbook in project window and insert module.
  • Paste the below code on the newly opened text editor

.

Function calcEndDate(start_date, weekday_duration, weekend_duration)

    ratio = weekday_duration / weekend_duration
    temp_date = start_date
    day_name = ""

    For i = 1 To weekday_duration
        day_name = Format(temp_date, "dddd")
        If (day_name = "Saturday" Or day_name = "Sunday") Then
            temp_date = temp_date + 1 / ratio
        Else
            temp_date = temp_date + 1
        End If
    Next
    calcEndDate = temp_date

End Function

Now paste this formula in any cell, it will calculate the end date using start date and the duration

calcEndDate(start_date, weekday_duration, weekend_duration)

After that you have to save your workbook in xlsm (macro enabled excel workbook) format

For example for your first row it would be =calcEndDate(F3,B3,C3)

Then change the format of that column to mm/dd/yyyy hh:mm, so that you can know if a lesson is ending in half day.

Upvotes: 1

Related Questions