Date Formulas
The most important thing to understand about dates is that Excel stores dates as serial numbers. The serial number represents the number of days from (the imaginary) day of 1/0/1900.
You can find the number of days between two dates by simply subtracting the dates:
You can also add or subtract days to a date:
To return today's date, use the TODAY Function. There is no input required for the TODAY Function:
=today()
DAY, MONTH, and YEAR Functions
The DAY, MONTH, and YEAR Functions return the day, month, and year of a given date. The only input required is a date. To calculate today's month:
=month(Today())
Date Function
The Date Function generates a date from a given year, month, and day.
=date(2019,5,20)
EDATE Function
The EDATE Function "rolls-forward" or "rolls-back" a date a certain number of months, returning the same day of the month as the original date. This is useful if you want to jump to the 1st, 15th, or other day in another month.
When working with dates, you should always make use of smart date functions. Enter a date once and use formulas like EDATE to develop all other dates from that first date. This makes your spreadsheets easier to update and review!
EDATE works great when working with the 1st or 15th of the month. However, It does not work well when dealing with the last day of the month because not every month has the same number of days. When working with the last day of the month, use EOMONTH instead
EOMONTH Function
The EOMONTH Function works similarily to the EDATE Function, except instead of returning the same day of the month as the input date, it will return the last day of the month. Let's look at some examples:
Return the last day of this month:
=eomonth(today(),0)
Return the first day of next month:
=eomonth(today(),0)+1
Return the first day of the current month:
=eomonth(today(),-1)+1
Your turn:
ليست هناك تعليقات:
إرسال تعليق