Building a Dynamic Calendar II: Let’s go on DATES

Hi friend! I hope the past week blessed you, and this new week puts the last one to shame. If you’re a first time visitor here, welcome! The seat you’re in is yours.

Last week, we began building our calendar and I started by introducing you to the SEQUENCE function. The SEQUENCE function is part of the larger complex formula below that will give us our calendar:

This week, I want to talk about some important date and time functions in Excel/Google Sheets that will help us understand and replicate the complex formula above.

Date and Time functions in Excel & Google Sheets

The most important thing to know about dates in Excel & Google Sheets is that each date has an associated numerical value. Excel uses these associated values to calculate the passage of time. For example, dates start from 1st January 1900 to 9999 (Yep! there is Excel in heaven). 01/01/1900 is assigned a value of 1 and today (15/11/2021) is the 44,515th day since 01/01/1900. To show you understand this, pause here and type your birth date into any cell. While that cell is selected, press CTRL + 1 and change the format of the cell to a number. You should see the associated value for your birth date. Congratulations, you now have a new PIN to use, or a new tattoo to get.

TODAY() and NOW()

Today() and Now() will give you the current date and current time respectively.

YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions

The YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions extract individual components of a given date:

THE EOMONTH FUNCTION

The EOMONTH function calculates the last day of a given month, or the start or end dates of previous or future months. In the example below, the formula would return the date of the last day of August 1969.

Extracted from Google Sheets

The WEEKDAY function

If you want to know what day of the week a given date falls on, use the WEEKDAY function.

Extracted from Excel Maven

WORKDAY and NETWORKDAYS

These 2 functions are useful for calculating things like when your leave ends, the number of days until your wedding, or the number of days since you submitted that proposal…

WORKDAYS returns a date that is a specific number of days before or after a given start date, excluding weekends and (if you want) holidays; NETWORKDAYS counts the number of workdays between two dates.

WORKDAY function arguments. Extracted from Excel Maven

NETWORK days in action. Extracted from Excel Maven

At this point, pause here and use WORKDAYS to find the date 60 days from now when the curfew in Kampala might be lifted. Additionally, use NETWORKDAYS to find out how many days there are between today and Valentine’s Day next year? How much time do you have to sell dreams?

DATE function

The DATE function is used when you need to take three separate values and combine them to form a date. The arguments the DATE function takes are:

=DATE(year,month,day)

Bonus feature

If you want to quickly type today’s date, the shortcut is CTRL + ;

Conclusion

Date and time functions are useful and often forgotten. Using them strategically can make tasks like project management, making timesheets, and calculating paid time off much faster. At this point, see if you can demystify the formula for our calendar and replicate it:

If you are stuck, we will look at this next week.

Be blessed!