# Date & Day issue

1. ## Date & Day issue

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

2. ## Re: Date & Day issue

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

3. ## Re: Date & Day issue

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.``

4. ## Re: Date & Day issue

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

5. ## Re: Date & Day issue

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

6. ## Re: Date & Day issue

Originally Posted by 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.``
For some reason it's not working, I'm not sure if I'm doing anything wrong.
Thanks

7. ## Re: Date & Day issue

Originally Posted by 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
Hi Robert,

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

8. ## Re: Date & Day issue

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

9. ## Re: Date & Day issue

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.

10. ## Re: Date & Day issue

Originally Posted by dav2020
=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"))

11. ## Re: Date & Day issue

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

Originally Posted by Trebor76
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)]

Originally Posted by Trebor76
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.``

12. ## Re: Date & Day issue

you guys are amazing.
Thank you so much

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1