Date and time are also widely used in Excel. If you can master its application skills proficiently, it will be of great help to improve statistical efficiency.
1. Excel date and time function: Now.
function: returns the current date and time.
syntax structure: =Now().
Purpose: returns the current date and time.
Method:
Enter the formula in the target cell: =NOW().
Interpretation:
1. This function has no parameters.
2. The returned result is the date and time set in the Windows system. Therefore, if the returned date and time is incorrect, you should first check the date and time in the Windows system.
3. The returned date and time will not be updated in real time unless the worksheet is recalculated.
2. Excel date and time function: Today.
function: returns the current date.
syntax structure: =Today().
Purpose: returns the current date.
Method:
Enter the formula in the target cell: =TODAY().
Interpretation:
1. This function has no parameters.
2. In addition to using the Today function to get the current date, you can also use the shortcut key Ctrl+; quickly enter the current date.
3. The returned result is the date and time set in the Windows system. Therefore, if the returned date and time are incorrect, you should first check the date and time in the Windows system.
4. The returned date and time will not be updated in real time unless the worksheet is recalculated.
3. Excel date and time function: Date.
function: returns the serial number of the specified date.
syntax structure: =Date("year","month","date").
Purpose: forms the corresponding date for the given "year", "month", and "day".
Method:
Enter the formula in the target cell: =DATE(F3,G3,H3).
Interpretation:
1. All parameters in the Date function can be directly entered numbers or cell references.
2. The value of the parameter "Year" must be between 1900 and 9999. If it is greater than 9999, the error value "#VALUE!" is returned. "Moon" and "Day" are different. Although the normal range of "Moon" is 1~12 and the normal range of "Day" is 1~31, the Date function has an automatic correction function for "Moon" and "Day". That is, if the "month" is greater than 12, Excel will automatically convert it to the next year; if the "day" is greater than 31, Excel will automatically convert it to the next month. Similarly, if both "month" and "day" are less than 1, Excel will convert it to the previous year or the previous month.
4. Excel date and time function: Time.
function: returns the serial number of the specified time.
Syntax structure: =Time("time","minute","second").
Purpose: calculates the meeting time after 3 hours and 30 minutes.
Method:
Enter the formula in the target cell: =TEXT(NOW(),"hh:mm")+TIME(3,30,0).
Interpretation:
1. All parameters in the Time function can be directly entered numbers or cell references.
2. For the parameter "time", any value greater than 23 will be divided by 24, and the remainder will be taken as the hour; for "minutes", any value greater than 59 will be converted to hours and minutes; for "seconds", any value greater than 59 will be converted to minutes and seconds.
3. In the formula, first use Text to get the formatted current date, then add a time function with the Time function, and finally calculate the meeting time.
5. Excel date and time function: Year.
function: returns the year in the date.
Syntax Structure: =Year (the date or cell reference to the year to be extracted).
Purpose: extracts the "year" in the sales date.
Method:
Enter the formula in the target cell: =YEAR(F3).
Interpretation: The parameters of the
Year function can be the sequence number, date text or cell reference that represents the date.
6. Excel date and time function: Month.
function: returns the month in the date.
Syntax Structure: =Month (to extract the date or cell reference of the month).
Purpose: extracts the "month" in the sales date.
Method:
Enter the formula in the target cell: =MONTH(F3).
Interpretation: The parameters of the
Month function can be the sequence number, date text or cell reference that represents the date.
7. Excel date and time function: Day.
function: returns the number of days in the date.
Syntax structure: =Day (the date or cell reference to the day to be extracted).
Purpose: extracts "day" in "sale date".
Method:
Enter the formula in the target cell: =DAY(F3).
Interpretation: The parameters of the
Day function can be the sequence number, date text or cell reference that represents the date.
8. Excel date and time function: Weekday.
function: returns the week corresponding to the specified date.
Syntax structure: =Weekday (date or cell reference, [statistical method]), where [statistical method] is divided into: 1 (number 1 (sunday) to digit 7 (saturday)), 2 (number 1 (month) to digit 7 (sunday)), 3 (number 0 (month) to digit 6 (sunday)).
Purpose: calculates the week corresponding to the date according to the regular calculation.
Method:
Enter the formula in the target cell: =WEEKDAY(F3,2).
Interpretation:
1, code "2" means to be calculated in order from Monday to Sunday (1-7).
2. When using the Text function to convert, "aaa" represents a short week and "aaaa" represents a long week.
9. Excel date and time functions: Hour, Minute and Second.
function: returns the number of hours in time.
Syntax structure: =Hour (time or cell reference), =Minute (time or cell reference), =Second (time or cell reference)
Purpose: Returns "time", "minutes", and "seconds" in time.
Method:
Enter the formula in the target cell: =HOUR(F3), =MINUTE(F3), =SECOND(F3).
Conclusion:
The article lists 11 Excel date and time functions to interpret their functions and basic syntax. Have you gotten it for application skills?
#I want to go to the headlines# #Excel function formula#11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
