Excel Date Formula Definition:
The Excel Date Formula returns a serial number that represents a particular date. While using excel, this is the correct way of providing a date.
Formula: DATE(year, month, day)
Explanation with Example Questions:
Today’s a sunny and bright day. Brenda decided to teach her children about the excel date formulas. She explained date functions that have regular usage.
- DATEDIF: Use in Calculating the number of days, months, or years between two particular dates. While calculating the age, use this formula.
- DATEVALUE: Converts the date in the form of a serial number or text.
- DAY: It converts the serial number to a particular day.
- DAYS: Calculates the number of days between two dates.
- EDATE: Returns the date that is the indicated number of months before or after the start_date.
- EOMONTH: Returns the serial number of the last day of the month before or after a specified number of months.
- MONTH: Converts serial number to month.
- NOW: Returns current date and time.
- TIME: Returns the time as per the values we provide in the formula.
- TODAY: Returns today’s date.
- YEAR: Separates the year from the date or converts a serial number into the corresponding year.
- TEXT: Change the format, i.e., convert a serial number of the date to the actual date.
Brenda gives questions for a better explanation of the concept of excel date formula.
Q: Print the date 2 May 2001, in Date Format.
Ans: This question will be solved using the excel date formula.
Formula: DATE(year, month, day)
Q: How to calculate the age in Excel?
Ans: This question will be solved using the DATEDIF function.
Suppose the DOB of a person is June 7, 2000, and we have to calculate the age by the date Aug 15, 2019.
Formula: DATEDIF(Initial date, Final Date, “d/m/y”)
** “d/m/y” means days/months/years. We can use one at a time. To calculate the difference of days between both dates, then write “d”; To calculate the difference of months writes “m”; for years use “y”.
- IMPORTANT: Before proceeding forward, it is necessary to understand what the serial number of dates in excel means. According to Microsoft, “Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, Jan1 1900, is serial number 1, and Jan1 2008, is serial number 39448 because it is 39,447 days after Jan 1, 1900.”
Q: Convert the date 7 February in the serial number.
Ans: This question will be solved using the DATEVALUE function.
Formula: DATEVALUE(date_text)
Q: Convert the serial number obtained in the last question (44599) into the date.
Ans: This question will be solved using the DAY function.
Formula: DAY(serial_number)
Q: Calculate the number of days between the dates 11/04/2003 and 23/06/2009.
Ans: This question will be solved using the DAYS function.
Formula: DAYS(end_date, start_date)
Q: Calculate the date four months after the date ’23 June 2007′.
Ans: This question will be solved using the EDATE function.
Formula: EDATE(start_date, months)
Q: Calculate which month is equivalent to serial number 44658.
Ans: This question will be solved using the MONTH function.
Formula: MONTH(serial_number)
Q: Print the current date and time.
Ans: This question will be solved using the NOW function.
Formula: NOW()
Q: Print the time 03:44 AM in time format.
Ans: This question will be solved using the TIME function.
Formula: TIME(hour, minute, second)
Q: Print today’s date.
Ans: This question will be solved using the TODAY function.
Formula: TODAY()
Q: Separates the year from the date 22/03/2019.
Ans: This question will be solved using the YEAR function.
Formula: YEAR(serial_number)
Q: Convert the serial number 43546 into the date format (mm/dd/yyyy).
Ans: This question will be solved using the TEXT function.
Formula: TEXT(value, format_text)
Carl and Carly both understood the concept of excel date function clearly and thanked their mother for teaching these concepts.
Carly missed the previous day’s lesson on percentages in excel, so she asked her mother, Brenda, to again teach her those concepts as she is also gaining interest in excel.