Need some help with Excel and automatic dates

You may try something like this...

In A12

=WORKDAY(EOMONTH(DATE($B$4,$B$3,1),-1),1)

Select allOpen in new window

In A13

=WORKDAY(A12,1)

Select allOpen in new window

and copy down the formula in A13.

See attached replicating your screenshots.

I have used the following formula to force the first day to be the first Monday of the month:

A12 =IF(WEEKDAY(DATE(B4,B3,1),1)>5,FLOOR(DATE(B4,B3,1),7)+2,DATE(B4,B3,1))

Subsequent cells then use the WEEKDAY function:

A13 =IF(A12="","",IF(MONTH(WORKDAY(A12,1))>MONTH(A12),"",WORKDAY(A12,1)))
Weekdays.xlsx

I prefer Neeraj's suggestion for the first working day.

Our suggestions for subsequent days are basically the same but mine will allow the formula to be copied down as required and will stop at the last working day of the month.

Do you need to allow for holiday days?

WORKDAY function has option to include a list of holidays if required.

Hallo Rob,
looks very good, but the first day is a Sunday.
Take a look at your program.
Best regards,
Thomas

Hallo Subodh Tiwari
=WORKDAY(EOMONTH(DATE($B$4,$B$3,1),-1),1) will not work...error in Formula

Slight change to the WEEKDAY function within that first formula:

=IF(WEEKDAY(DATE(B4,B3,1),2)>5,FLOOR(DATE(B4,B3,1),7)+2,DATE(B4,B3,1))

Second parameter of WEEKDAY function determines day 1 of the week.
Setting parameter to 1 (like I did originally) numbers 1 to 7 starting on Sunday
Setting parameter to 2 (like amended) numbers 1 to 7 starting on Monday

Neeraj's suggestion for the first day is probably better because it can include the option for holidays if required.

WORKDAY function has option to include a list of holidays if required.
I need only workdays from Monday - Friday, including holidays...if that is a Monday - Friday.
Thank you.

Not sure why you are getting error with the suggested formula.

Please refer to the attached.

DateFormula.xlsx

=IF(WEEKDAY(DATE(B4,B3,1),2)>5,FLOOR(DATE(B4,B3,1),7)+2,DATE(B4,B3,1))

Thats looks very good,

looks good, but you are missing Feb 28 and Feb 29

See attached with update for including holidays. The holiday dates are listed on a separate sheet, these are UK public holidays so may need changing for your location.

I have demonstrated both my and Neeraj's suggestion for subsequent dates. Mine in column A and Neeraj's in column C.

You'll see that the two columns are both populated as far as row 35 with formulas but mine doesn't show the days in subsequent month.

Neeraj's formula should work for the first day and is better than mine as it will allow for holidays, amended his formula to include holidays.
Weekdays.xlsx

Rob, something is wrong, I tried year 2020 look at the month

You will have to expand the column width to see the whole date.

Alternatively change the format so that the day and month words are abbreviated

I have tweaked the formula. See if this works now.

DateFormula.xlsx

The A row is better for me, because it shows only the dates in that month. Row C shows also (in some month) dates of the next month.
But I have the same error with  month 9/11/12

I have tweaked the formula. See if this works now.
thank you....but you are missing in almost every month the last days f.e. June 30 etc.

But I have the same error with  month 9/11/12

Which error are you referring to?

Dates showing as ################ - expand the column width
Dates stopping before end of month - copy formulas down further. The absolute max number of working days in any month is 23 so need to go down to row 35 (starting at 12, plus 23 = 35)

You will have to expand the column width to see the whole date.

Alternatively change the format so that the day and month words are abbreviated
sorry stupid mistake from me.
Looks almost perfect, only in month 12 it show the first day of the next year also

Changed that in the latest upload as I spotted that myself:

Change formulas in A13:
=IF(A12="","",IF(MONTH(WORKDAY(A12,1,Holidays!$A$2:$A$9))<>MONTH(A12),"",WORKDAY(A12,1,Holidays!$A$2:$A$9)))

Month check was only checking if month was greater than prior month. Dates for January in subsequent year the month is less than previous month so changed it to "not equal to" rather than "greater than".

EDITED to fix absolute cell references in second WORKDAY function

Minor tweak to formula in A13 and subsequent adopting parts of Neeraj's formula:

=IFERROR(IF(MONTH(WORKDAY(A12,1,Holidays!$A$2:$A$9))<>$B$3,"",WORKDAY(A12,1,Holidays!$A$2:$A$9)),"")

Actually all you need is, copy the formula to further down the rows to show all possible dates in a month. That's it.
sorry....I am very new with excel....and I have these program of a co-worker and try to improve it.
It works just fine.

Rob,
can you send me your Weekdays.xlsx with the new formula?
Because if I change it, I got an error.
The code from Subodh Tiwari is fine, only the month 12 shows the day of the next year, too

Thank you both for your great help.
You were a great help for me.

Very new with Excel....and I have these program of a co-worker and try to improve it.

Best regards from Germany
Thomas

Glad to help, if you have any further questions on this project please post them here. For subsequent projects start a new question thread so that the projects don't get muddled.

For future reference it is better to upload a sample file rather than showing screenshots.

Regards
Rob

Dear Rob,
I tried to add the code to my excel. The first row A12 will work...but not the A13 and so on.
I have enclosed a shorter version of the program.
Perhaps you can take a look.
Thank you very much
Best regards,
Thomas
Zeiterfassung-2020.01-Expert-Exchang.xls

Updated your file with formulas.

Used Neeraj's formula for first day as it allowed for holidays at the start of the month. Added the Holidays on 'Ferien' tab, googled public holidays in Germany and changed the dates to those, hope that Google translate translated the names of the days correctly.

Also set Data Validation on your Monat tab so that the month can only be a whole number from 1 to 12 and the year has to be a whole number greater than 1900.

The reason for the validation on the month is that the DATE function is a bit too clever for its own good. As you've seen the syntax is

DATE(Year, Month, Day).

If you were to put a number greater than 12 as the month, it wouldn't throw an error; it would add the first 12 months as a year and then the remaining as months, so entering year 2020 and month 13 would give a date in January 2021.
Zeiterfassung-2020.01-Expert-Exchang.xls

See attached file with further updates. I have amended all of the time references to proper times, rather than separate hour and minute figures.
Zeiterfassung-2020.01-Expert-Exchang.xls

A12       =ARBEITSTAG(MONATSENDE(DATUM($B$4;$B$3;1);-1);1;Ferien!$A$2:$A$10)
A13...                =WENNFEHLER(WENN(MONAT(ARBEITSTAG(A12;1;Ferien!$A$2:$A$10))<>$B$3;"";ARBEITSTAG(A12;1;Ferien!$A$2:$A$10));"")

Good Morning Rob,
thank you very much for your new file. It is great.
Works perfect.
Last question: how I have to change the A12 and A13 if I want the Sat and Sunday in the list, too?
I know the question was the opposite, but one of my co-workers told me, that sometimes they have to work on Sat or Sunday (perhaps 1-2 times in 10 years....or so)
Thank you very much.
Best regards,
Thomas

Dear Rob,
I will ask a new question for this...so you can get the extra points.
Best regards,
Thomas