Common date processing functions

When operating on date data, you often encounter operations such as obtaining the current date, obtaining the current time, obtaining the year, month, and day of the date, and so on. Let's introduce the common date processing functions.

Note: Here is the date function of FanRuan, which differs in SQL processing date. For example, SQL Server gets the date of the day as: GETDATE(), and the formula of FanRuan is today().

DATE(year,month,day): Returns a series number representing a specific date.

Note: if year, month, day are decimals, the decimals will be automatically removed and rounded, for example, the return value of 2001.5 is 2001, and the return value of 2001.1 is 2001. Only the integer case is discussed below.

Year: Represents the year.

If it is a positive integer, the absolute value +1 is taken if it is 0 or a negative integer. For example: year=-5, the return value of year is 6, which is (|-5|+1).

Month: Represents the month.

If month is an integer from 1 to 12, the function takes the parameter value as the month.

If month>12, the function starts from January of the year and accumulates upwards. For example: DATE(2000,25,2) returns 2002-01-02.

If month=<0, the function will subtract the corresponding number of months from December of the previous year. For example: DATE(2000,0,2) returns 1999-12-02; DATE(2000,-1,2) returns 1999-11-02.

Note: The date is the same. If it exceeds the maximum number of days, it will be added up from the first day of the month; if it is less than the number of days at the beginning of the month, the corresponding number of months will be subtracted from the end of the previous month. For example, the return value of DATE(2000,3,0) is the end of February 2000, that is, 2000-02-29.

Day: Represents the day.

If the date is less than or equal to the number of days in a specified month, the function uses this parameter value as the day.

If the date is greater than the number of days in a specified month, the function starts accumulating from the first day of the specified month. If the date is greater than the total number of days in two or more months, the function adds the remainder after subtracting two or more months to the third or fourth month, and so on. For example: DATE(2000,3,35) returns 2000-04-04.

Note:

If you need to process part of the date in the formula, such as year or month, you can use this formula.

If the year, month, and day are functions rather than constants in the function, this formula best reflects its effect.

Example:

DATE(1978, 9, 19) returns 1978-09-19.

DATE(1211, 12, 1) returns 1211-12-01.

Enter in the cell: =date(2011,1,10), the return value is 2011-01-10.

If the month is greater than 12, it will accumulate from January of the year. For example, enter: =date(2011,13,10) in the cell, and the return value is 2012-01-10.

1. Current date (year, month, day)

today() is to get the current date.

Enter =today() in the cell. If the system date is November 11, 2011, the return value is 2011-11-11.

2. Current time (hours, minutes and seconds)

now() Get the current date and time.

Enter =now() in the cell, the return value type is date and time, yyyy-MM-dd hh:mm:ss; for example: now(), cell format: normal, the returned value 2012-06-20 14: 55:57.

Get the year, month, and day of the date through the three functions: year(), month(), and day().

• Get the year: enter =year("2011-11-11") in the cell, and the return value is 2011.

• Get the month: enter =month("2011-11-11") in the cell, and the return value is 11.

• Get the date: enter =day("2011-11-11") in the cell, and the return value is 11.

Note: If year(), month(), day() correspond to year\month\day with decimals, it will return the current year or month or day, which is equivalent to year(today()), month(today()), day(today()).

You can use the todate() function to convert any date format data into a date format, or use the format() function to convert any date format data.

1. Convert date format string into date format

todate() can convert any date format data into date format, namely: yyyy-MM-dd data.

Enter =todate("2011/11/11") in the cell, and the return value is 2011-11-11.

Enter =todate("2/15/11","MM/dd/yy") in the cell, and the return value is 2011-02-15.

2. Convert date data into other date formats

format() can convert date data into other forms of data.

Enter =format("2/15/2011","dd/MM/yyyy") in the cell, and the return value is 15/02/2011.

Enter =format("2/15/2011","yyyy-MM-dd") in the cell, and the return value is 2011-02-15.

The three functions datedelta(), monthdelta(), and yeardelta() can be used to add and subtract dates.

1) Addition and subtraction of the date: enter =datedelta(today(),-1) in the cell to return the date one day before the current date. If the system date is 2011-11-11, the return value is 2011-11-10 .

Note: -1 in the formula means subtract one day. If it is a positive number, it means add the corresponding number of days.

2) Addition and subtraction of month: Enter =monthdelta(today(),-1) in the cell, and return the date of the previous month of the current date. If the system time is 2011-11-11, the return value is 2011-10- 11.

3) The addition and subtraction of the year: Enter =yeardelta(today(),-1) in the cell, and return the date of the previous year from the current date. If the system date is 2011-11-11, the return value is 2010-11- 11.

The four functions dateinyear(), dateinquarter(), dateinmonth(), dateinweek() can be used to realize the day of the year/quarter/month/week.

1) You can use dateinyear(date, number) to get the day of a year.

Enter =dateinyear(today(),1) in the cell to return the first day of the current year. If the system date is 2011-11-11, the return value is 2011-01-01.

Enter =dateinyear(today(),-1) in the cell to return the last day of the current year. If the system date is 2011-11-11, the return value is 2011-12-31.

2) You can use dateinquarter(date, number) to get the day of a quarter.

Enter =dateinquarter(today(),1) in the cell to return the first day of the current quarter. If the system date is 2011-11-11, the return value is 2011-10-01.

Enter =dateinquarter(today(),-1) in the cell to return the last day of the current quarter. If the system date is 2011-11-11, the return value is 2011-12-31.

3) You can get the day of a month by dateinmonth(date, number).

Enter =dateinmonth(today(),1) in the cell to return the first day of the current month. If the system date is 2011-11-11, the return value is 2011-11-01.

Enter =dateinmonth(today(),-1) in the cell to return the last day of the current month. If the system date is 2011-11-11, the return value is 2011-11-30.

4) You can get the day of a week by dateinweek(date, number).

Enter =dateinweek(today(),1) in the cell to return the first day of the current week. If the system date is 2011-11-11, the return value is 2011-11-07.

Enter =dateinweek(today(),-1) in the cell to return the last day of the current week. If the system date is 2011-11-11, the return value is 2011-11-13.

The lunar(year,day,month) function can be used to convert the solar calendar into the lunar calendar.

Enter in the cell: =lunar(2011,10,11), the return is the lunar date corresponding to October 11, 2011, that is, the return value is September 15, Xinmao year.

Enter in the cell: =lunar(2011,10,1), the return is the lunar date corresponding to October 1, 2011, that is, the return value is the fifth day of September in Xinmao year.

Note: The time period supported by the lunar formula is 1900-2100.

1. Current month

MONTH(today())

2. Same time last year

YEARDELTA(today(),-1)

3. Last month of last month

date(year(today()),MONTH(today())-2,day(today()))

4. Last month

date(year(today()),MONTH(today())-1,day(today()))

5. Same period last year last month

date(year(today())-1,MONTH(today())-1,day(today()))

6. End of the month

DATEINMONTH(TODAY(),-1)

7. Early this month

DATEINMONTH(TODAY(),1)

8. End of last month

DATEINMONTH(MONTHDELTA(TODAY(),-1),-1)

9. Early last month

DATEINMONTH(MONTHDELTA(TODAY(),-1),1)