How to calculate a weekend as a working day

C

correygold

New Member
Joined
Feb 17, 2025
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Hi all,

Im trying to put together an A/L tracker for my mother in Laws business.

Ive done everything right, at the very end now and I've realised the tracker does not calculate the weekend as a working day. Her business has staff working on most weekends. No clue how to change this.

I've posted screenshots of every formula ive used that has a date formula in it. Could someone please take a look and help?

Thanks
Employee leave - Dashboard
YEAR2025EMPLOYEE NAMEJohn DoeDATE JOINED9/7/23MOT EXPIRY DATE1/0/00COS EXPIRY
MonthsSundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
January01020304050607080910111213141516171819202122232425262728293031
February01020304050607080910111213141516171819202122232425262728
March01020304050607080910111213141516171819202122232425262728293031
April010203040506070809101112131415161718192021222324252627282930
May01020304050607080910111213141516171819202122232425262728293031
June010203040506070809101112131415161718192021222324252627282930
July01020304050607080910111213141516171819202122232425262728293031
August01020304050607080910111213141516171819202122232425262728293031
September010203040506070809101112131415161718192021222324252627282930
October01020304050607080910111213141516171819202122232425262728293031
November010203040506070809101112131415161718192021222324252627282930
December01020304050607080910111213141516171819202122232425262728293031
KEY STATISTICS
Annual Leave remainingAnnual leaveSick leaveHalf DayCompassionate leaveMaternity LeaveUnpaid Leave
21740000
 

Attachments

  • Screenshot 2025-02-17 at 20.46.03.png
    Screenshot 2025-02-17 at 20.46.03.png
    139.1 KB · Views: 11
  • Screenshot 2025-02-17 at 20.47.56.png
    Screenshot 2025-02-17 at 20.47.56.png
    216.1 KB · Views: 11
Last edited by a moderator:
Sort by date Sort by votes
Please post a concise extract of data that shows your challenge and expected reults.
N.B. You can post an extract with the forum's tool names xL2BB.
What days are considered weekends?
Are you concerned about holidays?

T202502a.xlsm
ABCDEFG
2Sat 31-May-25Sun 1-JunMon 2-JunTue 3-JunWed 4-JunThu 5-JunFri 6-Jun
3
5b
Cell Formulas
RangeFormula
B2:AE2B2=A2+SEQUENCE(,DAY(EOMONTH(A2,1)))
Dynamic array formulas.
 
Upvote 0
Dave Patton said:
Please post a concise extract of data that shows your challenge and expected reults.
N.B. You can post an extract with the forum's tool names xL2BB.
What days are considered weekends?
Are you concerned about holidays?

T202502a.xlsm
ABCDEFG
2Sat 31-May-25Sun 1-JunMon 2-JunTue 3-JunWed 4-JunThu 5-JunFri 6-Jun
3
5b
Cell Formulas
RangeFormula
B2:AE2B2=A2+SEQUENCE(,DAY(EOMONTH(A2,1)))
Dynamic array formulas.
Click to expand...
Thanks for your response. I’ll look into uploading it properly tomorrow. Saturday and Sunday are considered a weekend but people can still work on those days and book them off. Not concerned about holidays either. Thanks
 
Upvote 0
It looks as though you are talking about cells D22, J22 etc, but there are no formulae in them.
 
Upvote 0
Try giving last priority for weekend conditional formatting.
If weekend conditional formatting is in the first priority further further conditions are not calculated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Creating a staff shift rota with business rules
Replies
0
Views
62
magnum55
M
R
  • Question Question
Calculating future date excluding Public Holiday, Weekends and Backdate specific date
Replies
1
Views
1K
etaf
etaf
B
  • Solved
Issue with finding time difference taking account of weekends and workdays.
2
Replies
15
Views
595
Joe4
Joe4
R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
3K
Romy
R
S
  • Question Question
Adding a key (So if someone clicks "P" in the excel, it will automatically turn that cell Purple with the letter P in it)
Replies
6
Views
2K
sheri54371
S

Forum statistics

Threads
1,226,850
Messages
6,193,341
Members
453,790
Latest member
yassinosnoo1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top