Working with dates in Excel

[Zakky]

Hi, I have tried and tried but cannot get Excel to do a task. I need to get the date for every Monday in a month and after the last Monday I need to get the date for the last day in the month. I would enter the starting date but then would like to copy the formula to the right so it automatically puts in the dates for Mondays and the last day, I would like to drag this to the right for the years ahead. It doesn't matter if the first Monday is not the 1st of the month. I need the last day in the month even if it's not a Monday.

e.g.
start date (column A)
02/1017 09/10/17 16/10/17 23/10/17 30/10/17 31/10/17 06/11/17 13/11/17 ........


Any help would be greatly appreciated!
Thank you

 

[barry houdini]

If you put the first date in A2 then try this formula in B2 copied across

=MIN(EOMONTH(A2+1,0),WORKDAY.INTL(A2,1,"0111111"))

 

[Zakky]

Barry, this formula is awesome...it works like a dream - does exactly what i want it to do. what part of the formula do i change if i wanted to change Monday to, say, Wednesday? I've never see the .INT before and the "0111111". I can not thank you enough!

 

[barry houdini]

what part of the formula do i change if i wanted to change Monday to, say, Wednesday?

The "0111111" defines the working days (Monday to Sunday, where 0 indicates a working day, 1 a non working day), so "0111111" means that within WORKDAY.INTL function only Monday is considered as a working day (because of the zero), so here we are using it to give us the next Monday. To change to Wednesday, for example, you would put the zero at position 3, i.e. "1101111"

Look at the help for WORKDAY.INTL for more information

 

[Zakky]

Thank you for the prompt replies and the first-class support you have provided.