Date & Day issue

[abac]

Hi gurus,

hope you are well. Happy new year to you all.

very quick question : please can someone help me with the coding. I'm trying to achieve the following to have date & day.

- In Cell A1 -> Month there i.e. January
- I want in Cell B3 to B32- auto fill each date in dd/mm/yy format i.e. from 01/01/2020 to 31/01/2020
- I want in Cell B4 to B34 - it auto fill days for the above dates
However, it should stops after the last day of the month.

I'm sure for all of you it's the simplest thing and I'd really appreciate your help.

Many thanks,
abac

[Trebor76]

Hi abac,

Assuming the month in cell A1 is text and not a date formatted as a month and you want dates in Col. B and days in Col. C (or else the days will overwrite the dates) this should do the job:

Please Login or Register  to view this content.

Note too that the year used is from today's date.

HTH

Robert

[juddaaaa]

This should do what you want. Works from a blank Worksheet

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[dav2020]

if it works for you this can be done with just formulas on worksheet you don't need to use vb
attached example where you enter month and year column B will fill with days of the month entered

enter in B1 =IF($A$3<>"",IF($A$2<>"",DATE(A3,A2,1),""),"")
enter in B2 =IF(B1="","",IF(MONTH(B1+1)<>MONTH(B1),"",B1+1))
copy formula from B2 down to B32

enter in A1 =IF(OR(A2<1,A2>12),"",CHOOSE(A2,"January","February","March","April","May","June","July","August","September","October","November","December"))
enter month (number) in A2
enter year in A3

hope it helps

[abac]

thank you so much for your help - it doesn't give me days for those dates. Any idea how to do that ?

[abac]

This should do what you want. Works from a blank Worksheet

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thank you for your help.
For some reason it's not working, I'm not sure if I'm doing anything wrong.
Please can you help me.
Thanks

[abac]

Hi abac,

Assuming the month in cell A1 is text and not a date formatted as a month and you want dates in Col. B and days in Col. C (or else the days will overwrite the dates) this should do the job:

Please Login or Register  to view this content.

Note too that the year used is from today's date.

HTH

Robert

Hi Robert,

Thank you for your help.

I have copied the formula in VBA but for some reason it's not doing anything. The file is saved as XLSM and everythign is there but nothing is coming up.
please can you take a look.
many thanks

[dav2020]

to get full day name enter =TEXT(B1,"dddd") in cell C1 then copy formula from C2 down to C32
to get abbreviated day name enter =TEXT(B1,"ddd") in cell C1 then copy formula from C2 down to C32

[juddaaaa]

Have a look at this example. Clicking the button in the worksheet will fill in the sheet.

You can see how I've placed the codes in the Modules in the VBA editor.

[Rick Rothstein]

=IF(OR(A2<1,A2>12),"",CHOOSE(A2,"January","February","March","April","May","June","July","August","September","October","November","December"))

You might find this interesting... there is a shorter formula available that will do the same as your formula above.

=IF(OR(A2<1,A2>12),"",TEXT(29*A2,"mmmm"))

[Rick Rothstein]

.
And you might find these interesting as well (although I think you might already know them)...

intMonth = Evaluate("VLOOKUP(A1,{""January"",1;""February"",2;""March"",3;""April"",4;""May"",5;""June"",6;""July"",7;""August"",8;""September"",9;""October"",10;""November"",11;""December"",12},2,0)")

Much shorter...

intMonth = [MONTH(A1&1)]

intMonthDays = Day(DateSerial(Year(Now()), intMonth + 1, 1) - 1)

A smidgeon shorter...

intMonthDays = Day(DateSerial(Year(Now), intMonth + 1, 0))

By the way, your macro can be replaced with this shorter one (where I have chosen to output the day name instead of the day number in Column C)...

Please Login or Register  to view this content.

[abac]

you guys are amazing.
Thank you so much