Formulas used:=DATEVALUE(2020, 1, 1) //Converts to date format=DAY(1/1/2020) //Extracts day from the given date
=TODAY() //Returns the current date=NOW() //Fetches the current timestamp=DAYS("28/7/2020", "1/1/2020")
//Gives the number of days between 1/1/2020 and 28/7/2020
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”)
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)
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.
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()
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”)
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