Overview
When operating on date-type data, you often need to perform operations such as getting the current date, the current time, and the year, month, and day of a date.
This document introduces common date functions in FineReport.
Note:When the date formula is used as a parameter in a hyperlink, the original value with the hour, minute, and second will be transferred.
Getting Specified Date and Time
| Content to Be Obtained | Formula | Result | |
|---|---|---|---|
Getting the Current Time | |||
Current date (year-month-day) | =today() | If the system date is November 11, 2011, converts its type to YYYY-MM-DD and returns 2011-11-11. | |
Current time (hour:minute:second) | =now() | Returns 2012-06-20 14:55:57 if the cell format is general and the system time is 14:55:57, June 20, 2012. | |
Display the quarter in which the current month is located. | =roundup(month(today())/3) | If the system date is November 11, 2011, returns 4. | |
Obtaining the Year, Month, or Day of a Date | |||
Year | =year("2011-11-11") | Returns 2011. | |
Month | =month("2011-11-11") | Returns 11. | |
Date | =day("2011-11-11") | Returns 11. | |
Obtaining a Nth Day of a Year, Quarter, Month, or Week | |||
DATEINYEAR(date, number) gets the nth day of a year. | The first day of the current year. | =dateinyear(today(),1) | Returns 2011-01-01 if the system date is November 11, 2011. |
The last day of the current year. | =dateinyear(today(),-1) | Returns 2011-12-31 if the system date is November 11, 2011. | |
DATEINQUARTER(date, number) gets the nth day of a quarter. | The first day of the current quarter. | =dateinquarter(today(),1) | Returns 2010-10-01 if the system date is November 11, 2011. |
The last day of the current quarter. | =dateinquarter(today(),-1) | Returns 2011-12-31 if the system date is November 11, 2011. | |
DATEINMONTH(date, number) gets the nth day of a certain month. | The first day of the current month. | =dateinmonth(today(),1) | Returns 2011-11-01 if the system date is November 11, 2011. |
The last day of the current month. | =dateinmonth(today(),-1) | Returns 2011-11-30 if the system date is November 11, 2011. | |
DATEINWEEK(date, number) gets the nth day of a week. | The first day of the current week. | =dateinweek(today(),1) | Returns 2011-11-07 if the system date is November 11, 2011. |
The last day of the current week. | =dateinweek(today(),-1) | Returns 2011-11-13 if the system date is November 11, 2011. | |
Obtaining a Specified Time Relative to the Current Month | |||
Current month | =MONTH(today()) | Returns 11 if the system date is November 11, 2011. | |
The same day last year | =YEARDELTA(today(),-1) | Returns 2010-11-11 if the system date is November 11, 2011. | |
The month before last | =date(year(today()),MONTH(today())-2,day(today())) | Returns 2011-09-11 if the system date is November 11, 2011. | |
Last month | =date(year(today()),MONTH(today())-1,day(today())) | Returns 2011-10-11 if the system date is November 11, 2011. | |
The date one year and one month before the current date | =date(year(today())-1,MONTH(today())-1,day(today())) | Returns 2010-10-11 if the system date is November 11, 2011. | |
End of the last month | =ENDOFMONTH(TODAY(),-1) | Returns 2011-10-31 if the system date is November 11, 2011. | |
The beginning of the last month | =DATEINMONTH(MONTHDELTA(TODAY(),-1),1) | Returns 2010-10-01 if the system date is November 11, 2011. | |
YYYY-MM-DD HH:MM:SS in the last month | =date(year(left(NOW(),10)),MONTH(left(NOW(),10)) - 1,day(left(NOW(),10))) + RIGHT(NOW(),9) | If the system date is 2011-11-01 13:34:42, returns 2011-10-01 13:34:42. | |
Time Conversion
| Content to Be Obtained | Formula | Result |
|---|---|---|
Data Type Conversion | ||
Convert the character string of the date type to the time type. todate() can convert any date-type data to a time type YYYY-MM-DD. | =todate("2011/11/11") | If the system date is November 11, 2011, converts its type to YYYY-MM-DD and returns 2011-11-11. |
Convert time-type data to date type in other formats. format() | =format("2/15/2011","dd/MM/yyyy") | Returns 15/02/2011. |
Conversion from the Solar Date to the Lunar Date | ||
Convert the solar date to the lunar date. | =lunar(2011,10,11) | Returns the 15th day of the ninth lunar month in Xin MAO year, the corresponding lunar date of 2011-10-11. |
=lunar(2011,10,1) | Returns the 5th day of the ninth lunar month in Xin MAO year, the corresponding lunar date of 2011-10-01. | |
Addition and Subtraction of Dates
You can use datedelta(), monthdelta(), and yeardelta() to perform addition and subtraction on dates.
| Content to Be Obtained | Formula | Result |
|---|---|---|
Data Type Conversion | ||
Addition and subtraction of dates | =datedelta(today(),-1) Note: | Returns the date one day before the current date. For example, if the system date is November 11, 2011, returns 2011-11-10. Note: If the returned date format is like Tue Jul 29 00:00:00 CST 2008, you can use FORMAT functions to nest the DATEDELTA function, for example, FORMAT(DATEDELTA("2008-08-08",-10), "MM/dd/yyyy"). |
Addition and subtraction of months | =monthdelta(today(),-1) | Returns the date one month before the current date. For example, if the system date is November 11, 2011, returns 2011-10-11. |
Addition and subtraction of years | =yeardelta(today(),-1) | Return the date one year before the current date. If the system date is November 11, 2011, returns 2010-11-11. |
Previous：Common date processing functions
