Excel OfficeExcel Date and Time Functions return a dynamic date or time in a cell.
To return the next specific day of week (i.e. the next Wednesday, or Friday, or Monday) with a given start date, you can use a formula based on the WEEKDAY function. Formula =date+7-WEEKDAY(date+7-dow) Explanation In the example shown, the formula in D6 is: =B6+7-WEEKDAY(B6+7-2) Where B6 contains the date Friday, January 16, 2015, and 2 represents Monday, the “dow”…. read more »
To get the next scheduled event from a list of events with dates, you can use an array formula based on the MIN and TODAY functions to find the next date, and INDEX and MATCH to display the event on that date. Formula {=MIN(IF((range>=TODAY()),range))} Note: this is an array formula and must be entered with Control +… read more »
If you want to find the most recent Wednesday, or Saturday, or Monday, given a specific start date, you can use a formula that uses the MOD function. Formula =date-MOD(date-dow,7) Note: If the the date has the same day of week, date will be returned. Explanation Formulas that use the concept of “day of… read more »
To calculate months between two dates as a whole number, you can use the DATEDIF function. Formula =DATEDIF(start_date,end_date,”m”) Explanation In the example shown, the formula in D6 is: =DATEDIF(B6,C6,”m”) Note that the DATEDIF automatically rounds down. To round up to the nearest month, see below. The mystery of DATEDIF The DATEDIF function is a “compatibility”… read more »
To get the nth day if week in a month (i.e. the first Tuesday, third Tuesday, forth Thursday, etc.) you can use a formula based on the DAY and WEEKDAY functions. Formula =date-DAY(date)+1+n*7-WEEKDAY(date-DAY(date)+8-dow) Note: Day of week is abbreviated “dow” in the generic form of the formula. This is a number between 1 (Sunday) and 7… read more »
If you need to calculate the percent remaining in a year, based on a given date, you do so with a formula based on the YEARFRAC function. Formula =YEARFRAC(DATE(YEAR(date),1,1),date) Explanation In the example, the formula in C5, which calculates percentage of year remaining, is: =YEARFRAC(DATE(YEAR(B5),1,1),B5) How the formula works the YEARFRAC function takes two dates,… read more »
To get the nth day of year based on a given date, you can use a formula based on the DATE and YEAR functions. Formula =date-DATE(YEAR(date),1,0) Explanation In the example shown, the formula in C5 is: =B4-DATE(YEAR(B4),1,0) With the date “June 1, 2016” in cell B4, the formula returns 153, since June 1st is the 153rd day… read more »
To calculate a project start date or custom date based on an end date and duration in days, you can use the WORKDAY function. Note: WORKDAY function does not count the start date as a workday. Formula =WORKDAY(start,-days,holidays) Explanation In the example shown, the formula in E5 is: =WORKDAY(C5,-D5,holidays) where “holidays” is the named range G5:G9. How… read more »
If you want to calculate the quarter (i.e. 1,2,3,4) from a date, you can use the ROUNDUP function together with MONTH. Formula =ROUNDUP(MONTH(date)/3,0) Explanation In the example shown, the formula in cell C5 is: =ROUNDUP(MONTH(B5)/3,0) How this formula works In this case, the formula first extracts the month as a number between 1-12, then divides… read more »
To get the same date next month from a given date, you can use the EDATE function. Formula =EDATE(date,1) Explanation In the example shown, the formula in cell B5 is: =EDATE(B5,1) How this formula works EDATE can get the “same date” in the future or past, based on the number of months supplied. When 1… read more »