Date formula in excel regarding calendar

0

I need to fill in a Day and Date column in an Excel table to create a one month calendar:

Select a year:  2016
Select a month: September

-------------------------
| Day      | Date       |
|------------------------
|Thursday  | 01.09.2016 |
|Friday    | 02.09.2016 |
|  etc.    |    etc.    |
-------------------------

(Note the date format is dd.mm.yyyy.)

The days and dates must be calculated automatically after choosing year and month.

user596129

Posted 2016-09-27T09:19:43.700

Reputation: 39

What exactly do you need? Fill day and date? – Máté Juhász – 2016-09-27T09:32:01.973

Yes. To fill day and date but like it's in calendar for that month and year. – user596129 – 2016-09-27T09:35:59.983

1

Please note that https://superuser.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.

– DavidPostill – 2016-09-27T09:38:27.577

Answers

1

You can achieve it with a few simple formulas:

  • Date for first day of the month:
    =DATE(B1,B2,1)
  • Rest of the dates:
    =IFERROR(IF(MONTH(B5)=MONTH(B5+1),B5+1,""),"")
  • Day names:
    =IFERROR(CHOOSE(WEEKDAY(B5,2),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"),"")

Fill the formulas down to 31 rows, it'll display only dates in the month, cells below will be empty.

enter image description here

Máté Juhász

Posted 2016-09-27T09:19:43.700

Reputation: 16 807

Asked: 2016-09-27T09:19:43.700

Viewed: 125 times

Active: 2016-09-27T20:23:25.197