Dynamic Working and Week days remaining until end of month from a specific date in the month

Zakky

Board Regular
Joined
Mar 26, 2016
Messages
148
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • paydays.PNG
    paydays.PNG
    25.2 KB · Views: 21

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Something like this?

24 03 24.xlsm
CDEF
1Work daysWeek days
23/12/2023  
317/12/2023  
431/12/202300
514/01/2024  
628/01/202433
711/02/2024  
825/02/202444
910/03/2024  
1024/03/202457
117/04/2024  
1221/04/202479
135/05/2024  
1419/05/20241012
15
Days left
Cell Formulas
RangeFormula
E2:E14E2=IF(MONTH(C2)=MONTH(C3),"",NETWORKDAYS.INTL(C2,EOMONTH(C2,0)))
F2:F14F2=IF(E2="","",EOMONTH(C2,0)-C2)
 
Upvote 0
Something like this?

24 03 24.xlsm
CDEF
1Work daysWeek days
23/12/2023  
317/12/2023  
431/12/202300
514/01/2024  
628/01/202433
711/02/2024  
825/02/202444
910/03/2024  
1024/03/202457
117/04/2024  
1221/04/202479
135/05/2024  
1419/05/20241012
15
Days left
Cell Formulas
RangeFormula
E2:E14E2=IF(MONTH(C2)=MONTH(C3),"",NETWORKDAYS.INTL(C2,EOMONTH(C2,0)))
F2:F14F2=IF(E2="","",EOMONTH(C2,0)-C2)
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
You're welcome. Thanks for the follow-up.
.. and for your kind words. :)
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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