Tips & Tricks: Working with Date and Time in Google Sheets

Tips & Tricks: Working with Date and Time in Google Sheets
Reading Time: 3 minutes
Tricks and tips: Date and Time
Date and Time in Google Sheets

We will look at some commonly used tips, tricks and functions related to date and time in Google Sheets.

Date and Time in Google Sheets

Before we proceed you should know what is a timestamp. Timestamp is a string that contains data for a particular point in time. It contains the time, date and in some cases, also the day.

Date and Time in Google Sheets use a type of timestamp in the form of “DD/MM/YYYY HH/MM/SS“. This obviously changes based on the default date format based on your region.

Convert any format into a date format

There are a lot of formats for dates, so you might want a single format to make everything uniform and for general accessibility. For example, if you are using user-generated inputs into your sheet, they can be of various formats and you might want to convert them into a single format.

You can use the DATEVALUE function for this task

The syntax is as follows:

DATEVALUE(date_value)

Sample Usage:

DATEVALUE(‘1 Jan 2022”), DATEVALUE(“2022/1/1”)

Date value sample use
Figure 1: DATEVALUE sample use

Extract Day, Month and Year from a date

We can use DAY, MONTH and YEAR functions to extract from a date.

The syntax for the same is 

=DAY(date)

Sample Usage:

=DAY(A11), =DAY(DATE(1, 2, 2022), =DAY(1/1/2020)

Extracting day, month, and year from a date
Figure 2: Extracting Day, Month and Year from a date

You can also extract other features such as HOUR, SECOND etc.

Fetch Current Date in Google Sheets

Fetching the current date is pretty simple in Google Sheets, you just have to use the TODAY function. It displays the date. You can use this to extract the current day and current month as well.

Fetching current date
Figure 3: Fetching Current Date

Fetch Current Timestamp In Google Sheets

You can use the NOW function to return the current Timestamp. Additionally, you can use functions like MINUTE, SECONDS, to fetch the current minute and seconds. 

The syntax for the same is 

=NOW()

Fetching current timestamp
Figure 4: Fetching Current Timestamp

You can also extract a day, month and year from the timestamp.

Number of days between two dates

You can use the DAYS function to achieve the number of days between two dates.

The syntax for the same is as follows

=DAYS(date1, date2)

Sample Usage

DAYS(“2/28/2016”, “2/28/2017”)

Days between two dates
Figure 5: Days between two dates

Alternatively, you can also use DATEDIF function. You can also use this to find out the months or years between two dates.

Conclusion

We had a look at a few tricks and tips related to Date and Time in Google Sheets. You can also check out other articles in the section below.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Introduction to DATE function: Converts Year, Month and Day into Date

Numbers of Days, Months and Years between two dates: Number of Days, Months and Years between two dates

One response to “Tips & Tricks: Working with Date and Time in Google Sheets”

Leave a Reply