The TIMEVALUE function converts a time to a fraction of 1. Google Sheets works with times as fractions of a number, not as text strings. This function converts times into their usable form. Noon would be .5 and 6 PM would be .75.
The function can be used with 12 hour AM/PM time or with 24 hour time. If a date and time is given to the function, the date will be ignored, but the time will be converted. To represent seconds, use a second colon. 5 minutes and 12 seconds would be “00:05:12” not “05:12”.
Remember that dates are really just saved as numbers by your spreadsheet with date formatting applied. The date must be in a valid format, so you have to use the DATE function, wrap it in quotes, or use a cell reference to a cell containing a date.
Syntax
=TIMEVALUE(time_text)
time_text Text representation of a time.
Related functions
DATEVALUE – Converts a date to a numeric value in a similar way as TIMEVALUE.
The WEEKDAY function accepts a date and returns the day number of that date. The date must be in a valid format, so you have to use the DATE function, wrap it in quotes, or use a cell reference to a cell containing a date.
If your days, months, and years are in different cells, use the DATE function to combine them.
Syntax
=WEEKDAY(date,[type])
date – The date for which you want the week number. This is required.
[type] – Optional specification of which numbers scheme to use for the days of the week,
1 – Sunday = 1, Monday = 2, etc.
2 – Monday = 1, Tuesday = 2, etc.
3 – Monday = 0, Tuesday = 1, etc.
Related functions
DATE – Takes separate year, month, and day values and returns them as a date
The WEEKNUM function accepts a date and returns the week number (1 through 54) of that date. The date must be in a valid format, so you have to use the DATE function, wrap it in quotes, or use a cell reference to a cell containing a date.
If your days, months, and years are in different cells, use the DATE function to combine them.
Syntax
=WEEKNUM(date,[type])
date – The date for which you want the week number. This is required.
[type] – Optional specification of which day is the first day of the week. And, which week is the first week of the year.
1 (or not specified) – Week begins on Sunday
2 – Week begins on Monday
11 – Week begins on Monday (same as #2)
12 – Week begins on Tuesday
13 – Week begins on Wednesday
14 – Week begins on Thursday
15 – Week begins on Friday
16 – Week begins on Saturday
17 – Week begins on Sunday
21 – Week begins on Monday and the week containing the first Thursday of the year is numbered week 1. See ISOWEEKNUM for more.
Related functions
DATE – Takes separate year, month, and day values and returns them as a date
ISOWEEKNUM – Returns the ISO day of the week. Similar to WEEKNUM.
Errors
#NUM – The inputs are numbers but are not valid dates for the date input or the type input is out of range. This could happen if you use a negative number for the date or a number for type that is not one of the preset options (i.e. 1, 2, 11).
#VALUE! – The inputs are something that doesn’t convert to a number such as “The other day” or “Yester-yester-day”.
Examples
Live examples in Sheets
Go to this spreadsheet for the examples of the WEEKNUM function shown above that you can study and use anywhere you would like.
The DATEDIF function calculates the difference between two days. The difference can be returned in day, month, year or a combination thereof. Whereas the DAYS function will only return days, this function has multiple output options.
The input for this function needs to be either surrounded by quotes or a reference to another cell with a date in it. If your days, months, and years are in different cells, use the DATE function to combine them. If you want to know the current age, use TODAY() as the end date.
Related functions
DAYS – Simple duration of time formula with just days.
MINUS – Subtract one value from another. Can be done with dates.
Errors
#NUM – The starting date is after the beginning date.
Purpose
The DATEDIF function returns the number of day, month, or years between two dates.
Syntax
=DATEDIF(start_date,end_date,unit)
start_date Date at which to start the calculation
end_date Date at which to end the calculation
unit Type of output. Choices are “Y”, “M”, “D”, “YM”, “YD”, or “MD”.
Y – Number of whole years elapsed between start and end dates
YM – Number of months elapsed after the number of years shown with the “Y” unit. Will not exceed 11.
YD – Number of days elapsed after the number of years shown with the “Y” unit. No matter how many days after the last year, starts counting after end of last full year and is never over 364.
M – Number of whole months elapsed between start and end dates
MD – Number of days elapsed after the number of months shown with the “M” or “YM” unit. Can’t go higher than 30.
D – Number of whole days elapsed between start and end dates
Examples
Example 1 – Plain and Simple
Formula
Result
=DATEDIF(“March 1,2017″,”March 3, 2017″,”D”)
2
This function returns two days, the same as if you did ="3/3/17"-"3/1/17".
Example 2 – Different outputs
Examples showing DATEDIF being used with different parameters.
Live examples in Sheets
Go to this spreadsheet for the examples of the DATEDIF function shown above that you can study and use anywhere you would like.
The ISOWEEKNUM function accepts a date and returns the ISO week number (1 through 54) of that date. The date must be in a valid format, so you have to use the DATE function, wrap it in quotes, or use a cell reference to a cell containing a date.
An ISO week starts on Monday. The first week of the year is the first week containing a Thursday in that year.
If your days, months, and years are in different cells, use the DATE function to combine them.
Syntax
=ISOWEEKNUM(date)
date – The date for which you want the ISO week number. This is required.
Related functions
DATE – Takes separate year, month, and day values and returns them as a date
#NUM – The inputs are numbers but are not valid dates for the date input or the type input is out of range. This could happen if you use a negative number for the date.
#VALUE! – The inputs are something that doesn’t convert to a number such as “The other day” or “Yester-yester-day”.
The NETWORKDAYS function calculates the number of working days between two dates. The function excludes weekends (Saturday and Sunday) and can exclude holidays if you provide a list.
Syntax
=NETWORKDAYS(start_date,end_date,[holiday(s)])
start_date Date at which to start the calculation
end_date Date at which to end the calculation
[holiday(s)] An optional list of holidays to exclude from the count of days.
Related functions
DAYS – Calculate the number of days between two dates.
DATEDIF – Calculate length of time between two dates in years, months, and/or days.
MINUS – Subtract one value from another. Can be done with dates.
NETWORKDAYS.INTL – Calculate the number of work days between two dates. You can modify which days are weekends.
Errors
#VALUE! – The inputs are something that isn’t a valid date such as “The other day” or “Yester-yester-day”.
Examples
Live examples in Sheets
Go to this spreadsheet for the examples of the NETWORKDAYS function shown above that you can study and use anywhere you would like.
The NETWORKDAYS.INTL calculates the number of working days between two dates. The function excludes weekends (which may or may not be Saturday and Sunday) and can exclude holidays if you provide a list.
The WORKDAY.INTL function calculates a workday a given number of days away from a date. You can optionally specify a list of holidays to exclude from the count and different weekends. If your days, months, and years are in different cells, use the DATE function to combine them. This function is different from the WORKDAY function because you can also specify different weekends.
The DATEVALUE function takes a date value in any valid format and returns the same date as a serial number. All dates and times are stored as serial in Google Sheets so they will work in formulas (you can add March plus June).
The DATEVALUE function can be used to prepare dates to be sorted, filtered, or used in formulas.
The input for this function needs to be either surrounded by quotes or a reference to another cell with a date in it.
Purpose
The DATEVALUE function returns a serial number from any valid date.
Syntax
=DATEVALUE(date_text)
date_text – The date, written in a text format, that is to be converted
Related functions
TIMEVALUE – Converts a date to a numeric value in a similar way as DATEVALUE.
#VALUE! – The inputs are something that isn’t a valid date such as “A minute ago” or “Then”.
#NUM – An input is a number but is not a valid date.
Examples
Example 1 – Plain and Simple
Formula
Result
=DATEVALUE(“March 1, 2017”)
March 1, 2017
This example has a date-value that Sheets recognizes so it is able to return a serial number. In this case, the formatting is set to date, so the serial number is shown as March 1, 2017. If the formatting were set to number, the result of the function would be 42,795.00.
Example 2 – Unrecognized text date
Formula
Result
=DATEVALUE(“March 1st, 2017”)
#VALUE!
A full date typed as 1st instead of 1 returns a #VALUE! error. The #VALUE! error occurs whenever Sheets cannot figure out the text date.
Example 3 – Just a day and month
Formula
Result
=DATEVALUE(“March 1”)
March 1, 2017
Just a day and a month typed in. The spreadsheet assumes the date should be the current year which was 2017 when this was written. The year value will always be current when viewed in the live spreadsheet.
Example 4 – Used in an employee timesheet
Below is an example of this function being used to create an employee timesheet.
Live examples in Sheets
Go to this spreadsheet for the examples of the DATEVALUE function shown above that you can study and use anywhere you would like.
