Create Easy Yearly Calendar in Excel and Sheets with a SINGLE Formula

..

Nice:

Very nice! I like the color for alternating months--so easy! Conceptually, I've tried to figure out how to put a border between the months, and I figured it out while watching, again using Conditional Formatting. Now the formula might be a bit complex, in particular for the first of the month on a non-Sunday. But it's still doable. The only actual improvement to this would be to put the month as a side panel and just show the dates. For aesthetic looks for the month, I would link a formatted text box or shape (I really like rounded rectangles). Too bad shapes can't be sized dynamically without some VBA. Or can they?

Is there a way out to add data to this calendar.?

Hello Leila, what about this one where "year" is the cell where the wanted year value stands?
"=MAKEARRAY(31,12,LAMBDA(line,col,IF(MONTH(DATE(year,col,line))=col,DATE(year,col,line),"")))"

I have one question, Why doesn't excel have this calendar inbuilt in some applications !

You explain very clear and at pace anyone can keep up with, thank you from a Newbie.

Brilliant!!

Instead of the MOD function, you can just use ISODD or ISEVEN :)

Do you have any ideas on how to build a non-VB date picker for 64bit 365?

The formula displayed in this video clip is not equal to the one in the download workbook.
So which is the correct one?

Thanks Leila . I got an idea to prepare the report significant

interesting video - works as described - and I think an additional step in terms of making a calendar would simply be to add a blank row after each row of dates - then it would look exactly like a calendar

Awesome video. Thank you

how to add holidays for multiple countries in quarterly wise
can you please help me on this...

Okay mam but if there is a condition , i want to skip Sunday and Saturday and holidays how to create that type of calender

Who the hell starts the week on a sunday?

Is there a video on why this would be useful and what you could use it for?

Muchas gracias! era exactamente lo que estaba buscando :)

I have been watching many excel help videos, your teaching is the most clear and understandable! I really appreciated! From now on, I will keep on watching your videos! Love it!

my excel doesn't have =sequence 😢

wow! Brilliant!! Thank you so much

Great learning. Thanks for the video.

Hello Leila, can this dynamic calendar have split in twelve sheets. I mean each sheet have only month... Jan,Feb.....? Thanks for answer

Leila mam, can you make another tutorial to create a column-wise and Month-wise Calendar?

Leila Ma'am you are brilliant...

Thanks but it is Very complicated not as mensioned at the beginning 😕.

This can be enhanced to handle fiscal years. Below is an example with no helper cells.

Fiscal Year in cell D1. Fiscal Month End (enter 1 through 12 to represent calendar month) in cell D2. Sunday through Saturday labels in A5:G5.

In cell A6:
=SEQUENCE((53+IF(AND(WEEKDAY((IF(D2=12,DATE(D1,1,1),DATE(D1-1,D2+1,1))),1)=7,(DATE(D1,D2+1,0)-(IF(D2=12,DATE(D1,1,1),DATE(D1-1,D2+1,1)))+1)=366),1,0)),7,(IF(D2=12,DATE(D1,1,1),DATE(D1-1,D2+1,1)))-(WEEKDAY(IF(D2=12,DATE(D1,1,1),DATE(D1-1,D2+1,1)),1))+1,1)

First conditional format:
=OR(A6<(IF($D$2=12,DATE($D$1,1,1),DATE($D$1-1,$D$2+1,1))),A6>(DATE($D$1,$D$2+1,0)))
Stop if true

Second conditional format
=MOD(MONTH(A6),2)=1 (or 0)

Thank you very much. I learn a lot from your tutorials.

Thanks for this video it’s of great use to me. Well i have added week nos besides this as per ISO week norms. Could i request guidance on how can i start week numbering from first Thursday of financial year that is april at the same time calendar being dynamic

I am using the calendar in the data model but it didn't update with 2023 data. If I create a new calendar it appears but why isn't the existing calendar updating?

I need a calendar of Sundays only with each month on a different row.
Can you show me how to achieve that? Thanks,

Love you sweet Leila.

I tried the sequence function in WPS office and it didn't work I did everything right with the formula but when I pressed enter it didn't spill the numbers for the rows and columns. Does anyone know why when I pressed enter after entering the formula I only got a number one and not the other numbers...?

HELP!! I have a sheet with weekly headers, so I want the cells to show "Start date - End Date" for all 52 weeks....but I do it manually :/ Anyone know how to do that with a formula??

Thanks!

Always beautiful with good videos!!😍

Which version of excel are you using?

Instead of hardcoding 53, use WEEKNUM(END_OF_YEAR)-WEEKNUM(START_OF_YEAR)+1 in the first SEQUENCE parameter. It will give the correct rows (53 or 54) for the year.

The same concept can be applied for a single month, where you can use START_OF_MONTH and END_OF_MONTH to get the correct rows.

Great video as usual Leila, but what do you do for a LEAP YEAR? Please respond there are 2 previous questions about the same, still unanswered

🌺🌺Best work 📚📚📚

1 formula she said

Mam, can't we use Sequence function in my "mobile wps" please clarify.

Very good but I thought you were going to produce a calendar like the two to the right of the one you created. That's not part of Excel anymore unfortunately ☹

Trying to add a yearly vacation calendar (May to April) in a one page form with a quarter of the page showing the various months along with the day number. Any ideas?

These tutorials are amazing, thank you.

OMG. Great video