7 Ways to Make Auto Date Formulas in Excel and Examples
datetimeexcel.blogspot.com - There are 7 formulas for creating automatic dates in Microsoft Excel, namely the TODAY, NOW, DATE, MONTH, DAY, YEAR, and DAYS formulas. Fundamentally, to perform calculations Excel will convert date data into numeric data, so that it can be processed when creating automatic date formulas in Excel. The following is a brief explanation in the following table.
| Excel formulas | Uses |
| TODAY () | Today's date (dynamic) Excel formula |
| NOW () | Today's time and date Excel formula |
| DATE | Excel formulas write dates |
| MONTH | Excel formula to take the month in a cell |
| DAY | Excel formula to retrieve the date in a cell |
| YEAR | Excel formula to take the year in a cell |
| DAYS | Excel formulas calculate the difference between two dates |
| CTRL + ; | Shortcut to insert today's date (static) |
A. Excel TODAY () formula
The excel formula function TODAY () is to retrieve today's data from the internal date system on the computer being used. The TODAY () formula does not have any arguments ( independent syntax ). The standard date format used is month-date-year ( mm-dd-yy ).
Note: The TODAY () formulais dynamic, so the date changes the next day. To avoid this it is not recommended to use TODAY () . So, to speed up work when writing today's date and not change (static), you can use the shortcut CTRL +;
= TODAY ()
You can also perform calculations with mathematical operations with the TODAY () formula. Operations carried out using the amount of time of day. For example, we will find the date, 3 days later and 3 days ago by adding and subtracting it based on the date.
3 days later
= TODAY () +3
3 days ago
= TODAY () -3
B. Excel NOW () formula
Excel NOW () formula is a formula that is used to retrieve current date data and also displays the time in Excel automatically based on the computer's internal clock system. By default the format used is month-date-year-hour ( mm-dd-yy-yy ). You can also perform math operations using this formula, the default quantity used is days.
Note: The NOW () formulais dynamic, so the times change all the time.
Here's an example,
The formula calculates the current time
= NOW ()
Tomorrow's formula or addition by 1
= NOW () +1
Yesterday's formula or subtract 1
= NOW () -1
C. Excel DATE formula
Excel Date Formulas are functions that are used to define dates in Microsoft Excel. The following is the systematics of writing the DATE formula.
= DATE ( year , month , day )
- year is the year, when the year is defined with 2 digit numbers then Excel displays the 90s.
- month is the month written in numeric format
- day is the date
Example: Making December 31, 2017
= DATE ( 2017 , 12 , 31 )
D. Excel MONTH, DAY, and YEAR formulas
Excel MONTH, DAY and YEAR formulas are formulas used to retrieve a specific time in a cell containing date data. MONTH for the month, DAY for the date, and YEAR for the year. How to use the formula is very easy, as follows. bandarq online
= MONTH (cell) = DAY (cell) = YEAR (cell)
Example:
So that it is obtained
= MONTH (A1) = DAY (A1) = YEAR (A1)
E. Excel DAYS formula
The Excel DAYS formula is a formula used to calculate the difference between 2 dates. The following is the systematics of writing the DAYS formula. The resulting output is the number of days from the difference between the two dates in the DAYS function. The DAYS function calculates the difference in days based on 365 days / year.
= DAYS ( end_date , start_date )
- end_date is the last day that will be counted.
- start_data is the starting day that will be calculated.
IMPORTANT : The use of commas (,) or semicolon (;) in the FORMULA is adjusted to the version of Microsoft Excel and the language configuration on your computer.
For calculations with a 360 day / year system, you can use the DAYS360 function.
Example:
A hotel will calculate the lodging costs of 10 customers with a rental fee of IDR 125,000 per day which is illustrated in the following table.
= DAYS ( check-out , check-in )
Can use the DAYS formula and use multiplication and autofill operations
Komentar
Posting Komentar