Blog of Calc LLC

YEARFRAC function returns the number of fractional years between two days. Syntax YEARFRAC( start_date, end_date, [day_count_convention] ) The day_count_convention is optional and will be ignored in this tutorial. Examples The above result shows that there is 0.497 year between 1/1/2020 and 6/30/2020, or the duration between 1/1/2020 and 6/30/2020 is 49.72%.

YEAR function returns the year part of a date. For example, the year part of 2/20/2020 is 2020. Syntax YEAR(date) Examples

The WORKDAY  function answers questions like this: what date is it after 15 work days from 1/3/2020? WORKDAY function assumes that Saturday and Sunday are not work days. However, in some countries, the rest days may be Sunday and Monday. WORKDAY.INTL solve this problem. Syntax WORKDAY( date, number of work days, [weekend], [holidays] ) weekend is optional. weekend is a string of seven 0s and 1s, representing seven days from Monday to Sunday. 0 indicates work days and 1 indicates rest days. The default is "0000011", which means Monday to Friday are work days, while Saturday and Sunday are not weekend. If Sunday and Monday are rest days while Tuesday to Saturday are work days, we should use "1000001". Holidays  are optional If  number of work  days is negative, we calculate like what date it is 3 work days before this date Examples In the above example, it shows that 25 work days after 1/2/2020 is 2/13/2020 in China.

WORKDAY function answers questions like this: what date is it after 15 work days from 1/3/2020? Syntax WORKDAY( date, number of work days, [holidays] ) Holidays are optional If number of work days is negative, we calculate like what date it is 3 work days before this date Examples In the above example, it shows that 15 work days after 1/3/2020 is 1/24/2020. However, if we consider holidays, 15 work days after 1/3/2020 is 1/27/2020, because of Martin Luther King Day.

WEEKNUM  function returns the number of week of a date, that is, whether a date is the 5th, or 17th week of a year etc. Syntax WEEKNUM(date, [ type ]) type is optional.  If type is 1, it means the week starts on Sunday. The default value of  type  is 1. If type is 2, it means the week starts on Monday.  If  type  is 21, WEEKUM is equivalent to ISOWEEKNUM type  can be other values, which are rarely used. See the official reference if you need to use it. Example

WEEKDAY function tells whether a day is the 2nd, 4th day of the week etc. Syntax WEEKDAY(date, [ type ]) type is optional. If type is 1, Sunday is the 1st day and Saturday is the 7th day. If type is 2, Monday is the 1st day and Sunday is the 7th day. If type is 3, the value for Monday is 0 and the value for Sunday is 6. The default value for type is 1. Examples

TIMEVALUE function returns the percentage of time has pass for a day. For example, at 12:00 PM, 50% of the time of a day (24 hours) has passed. At 18:00 PM, 75% of the time of the day has passed. However, TIMEVALUE function does not return percentage directly. It returns a value between 0 and 1 such as 0.05 or 0.42. You can format it as percentage though. Syntax TIMEVALUE(time) Examples

TODAY function returns the current date. Syntax TODAY() Examples Notes TODAY function is a volatile function. The value it returns is the date when the spreadsheet is recalculated, not the date when the spread is created. Today only returns the current date without a time component. To create a time with date and time, use NOW function.

TIME function converts hour, minute, and second into a time. Syntax TIME(hour, minute, second) Examples

SECOND function returns the seconds part of a time. For example, the seconds part of 8:21:14 is 14. Syntax SECOND(time) Examples

NOW function returns the current date and time. Syntax Now() Examples Notes NOW is a volatile function. Each time the spreadsheet is recalculated, the result of NOW function will be updated. If you do not need the current time but only current date, use TODAY function instead.

NETWORKDAYS.INTL function calculate the net work days between two days for countries outside of US. Syntax NETWORKDAYS.INTL(start date, end date, weekend indicator, [holidays]) Weekend indicator is a string of seven 0s or 1s indicating which days of a week are weekends. 0 indicates work days and 1 indicates weekends. The first number indicates Monday. For example, "0000011" indicates Saturday and Sunday are weekends, while "1000001" indicates Monday and Sunday are weekends. Examples Notes You can use an integer 1 to 17 as the weekend indicator. However, that is not as visual as "0000011".

NETWORKDAYS function does NOT calculate "network days" such as the number of days your computer network has been on. It calculates "net work days" -- the number of days between two dates except weekend or holidays. Syntax NETWORKDAYS(start date, end date, [holidays]) [holidays] are optional. Examples Notes You need to provide holidays by yourself. If you do not do it, all holidays will be regarded as work days.

MONTH function returns the month part of a date. For example, the month part of 2/15/2020 is 2. Syntax MONTH(date) Examples Notes You can use any format for the dates, as long as they are valid dates. The MONTH function will return an error if the argument is not a valid date, such as "15/2/2019"

MINUTE function returns the minute component of a time. Syntax MINUTE(time) Examples Notes If you have already used the 24-hour system such as "17:48:21", you should not add "PM" after it. "17:48:21 PM" is not regarded as a time but a general text. MINUTE("17:48:21 PM")  gives a "#VALUE!" error.

ISOWEEKNUM returns the number of week of a date using ISO standard, that is, whether a date is the 5th, or 17th week of a year etc. Syntax ISOWEEKNUM(date) Example Notes Per ISO standard, a week begins on Monday and ends on Sunday. Under ISO standard, whether a week belongs to a years depending whether its Thursday falls in a year. Therefore, January 1 is not always week 1 of a year, it can be week 53 of the last year. Similarly, December 31 is not always week 52, it can week 53 of the current year, of week 1 of the next year.

HOUR function returns the hour component of a time. Syntax HOUR(time) Example Notes HOUR function uses the 24-hour system. Therefore, HOUR("5:03:24 pm")  returns 17, not 5. The seconds component in a time is optional. Therefore, 4:32 AM is equivalent to 4:32:00 AM. Actually, the minutes component is also optional. If you enter 4 am, it will be converted 4:00 am automatically.

EOMONTH() does two things at the same time. It shifts a date by a certain number of months and then moves to the last day of the month of the new date. Syntax EOMONTH(date, months) Example Notes If you set the months to 0 in EOMONTH() , you go to the end of the same month.

EDATE function shifts a date by a certain number of months. Syntax EDATE(date, number_of_months) Example Notes If the number of months is positive, EDATE function shifts a date to the future. If the number of months is negative, EDATE function shifts a date to the past. If the number of months is decimal such as 1.8, the decimal part will be dropped, so 1.8 is equivalent as 1.

DAYS function calculates the number of days between two dates. Syntax DAYS(end_date, start_date) Example Notes DAYS(end_date, start_date) is equivalent as DATEDIF(start_date, end_date, "D") In DAYS function, the first argument is end_date, the second argument is start_date, which is different from DATEDIF function.