Hi, I have dates with 14 days difference between them (employees paid every 2 weeks). Is there a way to automate the Working days and week days to the end of month from the last pay date. I am currently doing this manually using calendar which is open to errors and time consuming. Basically, i need to calculate separately how many working days and week days are remaining from the last pay day to end of the month. Your help would be greatly appreciated.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Dynamic Working and Week days remaining until end of month from a specific date in the month
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 63,880
- Office Version
- 365
- Platform
- Windows
Something like this?
| 24 03 24.xlsm | ||||||
|---|---|---|---|---|---|---|
| C | D | E | F | |||
| 1 | Work days | Week days | ||||
| 2 | 3/12/2023 | |||||
| 3 | 17/12/2023 | |||||
| 4 | 31/12/2023 | 0 | 0 | |||
| 5 | 14/01/2024 | |||||
| 6 | 28/01/2024 | 3 | 3 | |||
| 7 | 11/02/2024 | |||||
| 8 | 25/02/2024 | 4 | 4 | |||
| 9 | 10/03/2024 | |||||
| 10 | 24/03/2024 | 5 | 7 | |||
| 11 | 7/04/2024 | |||||
| 12 | 21/04/2024 | 7 | 9 | |||
| 13 | 5/05/2024 | |||||
| 14 | 19/05/2024 | 10 | 12 | |||
| 15 | ||||||
Days left | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2:E14 | E2 | =IF(MONTH(C2)=MONTH(C3),"",NETWORKDAYS.INTL(C2,EOMONTH(C2,0))) |
| F2:F14 | F2 | =IF(E2="","",EOMONTH(C2,0)-C2) |
Upvote
0
Peter_SSc, thank you for the prompt reply. The solution is amazing and works like a dream...saved me lots of time and effort. There are amazing people on this platform and they make a huge impact in other people's everyday lives at work. Have a great day!
Upvote
0
