Date formulas
Days between dates, working days, ages, month ends, quarters and adding months. 102 answers, each checked by running it, with a live example and an exercise.
How to calculate the number of days between two dates in Excel
=C2-B2Subtract the earlier date from the later one: =C2-B2. Dates are numbers of days, so the difference is the number of days.
How to count working days between two dates in Excel
=NETWORKDAYS(B2,C2,$F$2:$F$3)Use =NETWORKDAYS(start, end, holidays). It counts Monday to Friday, both ends included, and skips any dates listed as holidays.
How to calculate age from a date of birth in Excel
=DATEDIF(B2,$F$1,"y")Use =DATEDIF(birth_date, as_of_date, "y"). It returns completed years, so the age only goes up on the birthday.
How to add months to a date in Excel
=EDATE(B2,C2)Use =EDATE(start_date, months). It moves the date by whole months and handles month ends correctly.
How to get the last day of the month in Excel
=EOMONTH(B2,0)Use =EOMONTH(date, 0). The 0 means this month; 1 is next month and -1 is last month.
How to get the month name from a date in Excel
=TEXT(A2,"mmmm")Use =TEXT(date,"mmmm") for the full name or "mmm" for the short one.
How to calculate the number of weeks between two dates in Excel
=INT((C2-B2)/7)Divide the days by 7 and drop the remainder:
=INT((C2-B2)/7)returns the number of complete weeks.How to calculate the number of months between two dates in Excel
=DATEDIF(B2,C2,"m")Use
=DATEDIF(start, end, "m"). It counts complete months, so a month only counts once the same day of the month comes around again.How to calculate age in years, months and days in Excel
=DATEDIF(B2,$F$1,"y")&" years, "&DATEDIF(B2,$F$1,"ym")&" months, "&DATEDIF(B2,$F$1,"md")&" days"Join three DATEDIFs:
"y"for years,"ym"for the months left over and"md"for the days left over.How to calculate years of service in Excel
=DATEDIF(B2,$F$1,"y")&" years, "&DATEDIF(B2,$F$1,"ym")&" months"Use
=DATEDIF(hire_date, as_of, "y")&" years, "&DATEDIF(hire_date, as_of, "ym")&" months"for completed years and months.How to calculate the number of days until a deadline in Excel
=B2-$F$1Subtract today's date from the deadline:
=B2-$F$1, with today's date in F1. Past deadlines come out negative.How to calculate days overdue in Excel
=MAX(0,IF(C2="",$G$1,C2)-B2)Count from the due date to the paid date, or to today if unpaid, and floor it at zero:
=MAX(0,IF(C2="",$G$1,C2)-B2).How to add days to a date in Excel
=B2+C2Add the number to the date:
=B2+C2. Dates are counts of days, so this moves the date forward that many days.How to add weeks to a date in Excel
=B2+C2*7Multiply the weeks by 7 and add them:
=B2+C2*7.How to add years to a date in Excel
=EDATE(B2,C2*12)Use EDATE with 12 months per year:
=EDATE(B2,C2*12).How to add business days to a date in Excel
=WORKDAY(B2,C2,$F$2:$F$4)Use
=WORKDAY(start, days, holidays). It skips Saturdays, Sundays and every date in the holiday list.How to move a date that falls on a weekend to the next business day in Excel
=WORKDAY(B2-1,1,$E$2:$E$3)Step back one day and forward one working day:
=WORKDAY(B2-1,1,holidays). Weekdays stay put; weekends and holidays move forward.How to get the first day of the month in Excel
=EOMONTH(B2,-1)+1Use
=EOMONTH(B2,-1)+1: the day after the end of last month is the first of this month.How to find the last business day of the month in Excel
=WORKDAY(EOMONTH(A2,0)+1,-1,$D$2)Go to the first day of next month and step back one working day:
=WORKDAY(EOMONTH(A2,0)+1,-1,holidays).How to find the first Monday of the month in Excel
=LET(first,EOMONTH(A2,-1)+1,first+MOD(2-WEEKDAY(first),7))Take the first of the month and add the days to the next Monday:
=LET(first,EOMONTH(A2,-1)+1,first+MOD(2-WEEKDAY(first),7)).How to find the last Friday of the month in Excel
=EOMONTH(A2,0)-MOD(WEEKDAY(EOMONTH(A2,0))-6,7)Step back from the month end to the last Friday:
=EOMONTH(A2,0)-MOD(WEEKDAY(EOMONTH(A2,0))-6,7).How to find the 3rd Wednesday of a month in Excel
=LET(first,EOMONTH(A2,-1)+1,first+MOD($E$1-WEEKDAY(first),7)+7*($E$2-1))Find the first matching weekday, then add 7 days per extra week:
=LET(first,EOMONTH(A2,-1)+1,first+MOD($E$1-WEEKDAY(first),7)+7*($E$2-1)).How to get the date of the next Friday in Excel
=A2+7-MOD(WEEKDAY(A2)-$E$1,7)Add the days to the next Friday:
=A2+7-MOD(WEEKDAY(A2)-6,7), with the weekday number (6 for Friday) in a cell if it changes.How to get the Monday of the week from a date in Excel
=A2-WEEKDAY(A2,3)Subtract the days since Monday:
=A2-WEEKDAY(A2,3). WEEKDAY with 3 counts Monday as 0.How to calculate the week ending date in Excel
=A2+7-WEEKDAY(A2)Add the days left until Saturday:
=A2+7-WEEKDAY(A2). Every date in a Sunday-to-Saturday week gets the same week-ending date.How to get the day of the week from a date in Excel
=TEXT(A2,"dddd")Use
=TEXT(A2,"dddd")for the full day name, or"ddd"for Mon, Tue.How to check if a date falls on a weekend in Excel
=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")Use
=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday"). With type 2, Saturday is 6 and Sunday is 7.How to get the quarter from a date in Excel
="Q"&ROUNDUP(MONTH(A2)/3,0)Divide the month by 3 and round up:
="Q"&ROUNDUP(MONTH(A2)/3,0).How to calculate the fiscal quarter from a date in Excel
="Q"&INT(MOD(MONTH(A2)-$E$1,12)/3)+1Count the months since the fiscal year started, then divide by 3:
="Q"&INT(MOD(MONTH(A2)-$E$1,12)/3)+1, with the start month in E1.How to calculate the fiscal year from a date in Excel
=YEAR(A2)+(MONTH(A2)>=$E$1)Add 1 to the calendar year from the start month on:
=YEAR(A2)+(MONTH(A2)>=$E$1), with the start month in E1.How to calculate the number of working days in a month in Excel
=NETWORKDAYS(EOMONTH(A2,-1)+1,EOMONTH(A2,0),$D$2:$D$6)Use NETWORKDAYS from the first to the last day of the month:
=NETWORKDAYS(EOMONTH(A2,-1)+1,EOMONTH(A2,0),holidays).How to get the number of days in a month in Excel
=DAY(EOMONTH(A2,0))Take the day number of the month's last day:
=DAY(EOMONTH(A2,0)).How to check if a year is a leap year in Excel
=IF(MONTH(DATE(A2,2,29))=2,"Leap year","Common year")Ask DATE for February 29:
=IF(MONTH(DATE(A2,2,29))=2,"Leap year","Common year"). In a common year it rolls over to March.How to check if a date is between two dates in Excel
=IF(AND(B2>=$F$1,B2<=$F$2),"Yes","No")Test both ends with AND:
=IF(AND(B2>=$F$1,B2<=$F$2),"Yes","No").How to get the same date last year in Excel
=EDATE(A2,-12)Go back 12 months with EDATE:
=EDATE(A2,-12). February 29 becomes February 28.How to check if a date is in the current month in Excel
=IF(EOMONTH(B2,0)=EOMONTH($F$1,0),"Yes","No")Compare month ends:
=IF(EOMONTH(B2,0)=EOMONTH($F$1,0),"Yes","No")checks the month and the year at once, with today in F1.How to calculate month-to-date (MTD) sales in Excel
=SUMIFS(C2:C10,A2:A10,">="&EOMONTH(F1,-1)+1,A2:A10,"<="&F1)Use SUMIFS from the first of the month to the as-of date:
=SUMIFS(C2:C10,A2:A10,">="&EOMONTH(F1,-1)+1,A2:A10,"<="&F1).How to convert text to a date in Excel
=DATEVALUE(A2)Use
=DATEVALUE(A2). It reads text that looks like a date and returns a real date you can format and calculate with.How to convert YYYYMMDD to a date in Excel
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))Cut out the year, month and day and rebuild the date:
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)).How to convert dd/mm/yyyy text to a date in Excel
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))Rebuild the date from its parts:
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)). It works whatever your regional settings are.How to fix DATEVALUE returning #VALUE! in Excel
=IF(ISNUMBER(A2),A2,DATEVALUE(TRIM(A2)))Pass real dates through and clean the text before converting:
=IF(ISNUMBER(A2),A2,DATEVALUE(TRIM(A2))).How to remove the time from a date in Excel
=INT(A2)Use
=INT(A2). A timestamp is a date number plus a fraction for the time, and INT drops the fraction.How to combine text with a date in Excel
=A2&" starts on "&TEXT(B2,"mmmm d, yyyy")Format the date with TEXT before joining it:
=A2&" starts on "&TEXT(B2,"mmmm d, yyyy"). Without TEXT the date shows as a number.How to sort birthdays by month and day in Excel
=SORTBY(A2:B9,MONTH(B2:B9),1,DAY(B2:B9),1)Sort by the month, then by the day, and ignore the year:
=SORTBY(A2:B9,MONTH(B2:B9),1,DAY(B2:B9),1).How to find the latest date for each customer in Excel
=MAXIFS($B$2:$B$9,$A$2:$A$9,E2)Use MAXIFS:
=MAXIFS(dates, customers, E2)returns the most recent date for the customer in E2.How to return the amount of the most recent order for a customer in Excel
=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1)),C2:C9)Find the customer's latest date with MAXIFS, then look up the row with both:
=XLOOKUP(1,(A2:A9=F1)*(B2:B9=MAXIFS(B2:B9,A2:A9,F1)),C2:C9).How to calculate the first of the month after 60 days in Excel
=EOMONTH(B2+$F$1,0)+1Add the waiting period, go to that month's end and add a day:
=EOMONTH(B2+$F$1,0)+1, with 60 in F1.How to calculate the next billing date for a monthly subscription in Excel
=EDATE(B2,DATEDIF(B2,$F$1,"m")+1)Count the months billed so far and step one more:
=EDATE(B2,DATEDIF(B2,$F$1,"m")+1), with today in F1.How to calculate the days remaining in the month in Excel
=EOMONTH(A2,0)-A2Subtract the date from the month end:
=EOMONTH(A2,0)-A2.How to calculate the days left in the year in Excel
=DATE(YEAR(A2),12,31)-A2Subtract the date from December 31:
=DATE(YEAR(A2),12,31)-A2.How to get the day of the year from a date in Excel
=A2-DATE(YEAR(A2),1,0)Subtract the last day of the previous year:
=A2-DATE(YEAR(A2),1,0)returns 1 to 366.How to convert a date to a Julian date (YYDDD) in Excel
=TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000")Join the two-digit year and the three-digit day of the year:
=TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000").How to convert a Julian date (YYDDD) to a regular date in Excel
=DATE(2000+INT(A2/1000),1,MOD(A2,1000))Split the year and the day number with INT and MOD, then let DATE count the days:
=DATE(2000+INT(A2/1000),1,MOD(A2,1000)).How to count the number of Mondays between two dates in Excel
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(C2-B2+1,1,B2))=$G$1))List the dates with SEQUENCE and count the Mondays:
=SUMPRODUCT(--(WEEKDAY(SEQUENCE(C2-B2+1,1,B2))=2)).How to convert a date to month and year in Excel
=TEXT(A2,"mmm yyyy")Use
=TEXT(A2,"mmm yyyy")for a label like Jan 2026, or keep a real date with=EOMONTH(A2,-1)+1and a "mmm yyyy" format.How to extract the year from a date in Excel
=YEAR(B2)Use
=YEAR(B2). It returns the four-digit year as a number; MONTH and DAY return the other parts.How to create a date from separate year, month and day columns in Excel
=DATE(A2,B2,C2)Use
=DATE(year, month, day), for example=DATE(A2,B2,C2).How to calculate an end date from a start date and a term in months in Excel
=EDATE(B2,C2)-1Add the term with EDATE and subtract a day:
=EDATE(B2,C2)-1. A 12-month term from January 1 ends December 31.How to flag dates that expire within 30 days in Excel
=IF(C2<$F$1,"Expired",IF(C2-$F$1<=$F$2,"Expires soon","OK"))Test for expired first, then for the warning window:
=IF(C2<$F$1,"Expired",IF(C2-$F$1<=$F$2,"Expires soon","OK")).How to calculate the days until the next birthday in Excel
=DATE(YEAR($F$1)+(TEXT(B2,"mmdd")<TEXT($F$1,"mmdd")),MONTH(B2),DAY(B2))-$F$1Build this year's birthday, move it to next year if it has passed, and subtract today:
=DATE(YEAR($F$1)+(TEXT(B2,"mmdd")<TEXT($F$1,"mmdd")),MONTH(B2),DAY(B2))-$F$1.How to list all birthdays in a given month in Excel
=FILTER(A2:B11,MONTH(B2:B11)=E1,"No birthdays")Filter on the month number:
=FILTER(A2:B11,MONTH(B2:B11)=E1,"No birthdays").How to convert a Unix timestamp to a date in Excel
=B2/86400+DATE(1970,1,1)Divide the seconds by 86,400 and add January 1, 1970:
=B2/86400+DATE(1970,1,1).How to convert a date to a Unix timestamp in Excel
=(A2-DATE(1970,1,1))*86400Subtract January 1, 1970 and multiply by the seconds in a day:
=(A2-DATE(1970,1,1))*86400.How to list all dates between two dates in Excel
=SEQUENCE(B2-B1+1,1,B1)Use SEQUENCE starting at the start date:
=SEQUENCE(B2-B1+1,1,B1), and format the results as dates.How to create a list of the first day of each month in Excel
=EDATE(B1,SEQUENCE(B2,1,0))Move the start date forward 0, 1, 2 … months:
=EDATE(B1,SEQUENCE(B2,1,0)).How to create a list of working days in Excel
=WORKDAY(B1-1,SEQUENCE(B2),B3:B4)Combine WORKDAY with SEQUENCE:
=WORKDAY(B1-1,SEQUENCE(B2),holidays)lists the next working days, skipping weekends and holidays.How to get the previous business day in Excel
=WORKDAY(A2,-1,$D$2:$D$3)Count one working day back:
=WORKDAY(A2,-1,holidays).How to check if a date is a holiday in Excel
=IF(COUNTIF($E$2:$E$7,A2),"Holiday",IF(WEEKDAY(A2,2)>5,"Weekend","Workday"))Count the date in the holiday list:
=IF(COUNTIF(holidays,A2),"Holiday",IF(WEEKDAY(A2,2)>5,"Weekend","Workday")).How to get the end date of the quarter in Excel
=EOMONTH(A2,MOD(3-MONTH(A2),3))Move forward by the months left in the quarter and take the month end:
=EOMONTH(A2,MOD(3-MONTH(A2),3)).How to get the first day of the quarter in Excel
=DATE(YEAR(A2),ROUNDUP(MONTH(A2)/3,0)*3-2,1)Find the quarter's first month and build the date:
=DATE(YEAR(A2),ROUNDUP(MONTH(A2)/3,0)*3-2,1).How to convert a month name to a number in Excel
=MONTH(DATEVALUE(A2&" 1"))Turn the name into a date and take its month:
=MONTH(DATEVALUE(A2&" 1")).How to convert a month number to a month name in Excel
=TEXT(DATE(2026,A2,1),"mmmm")Build any date in that month and format it:
=TEXT(DATE(2026,A2,1),"mmmm").How to calculate the week of the month from a date in Excel
=INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/7)+1Offset the day number by the weekday of the 1st, then divide by 7:
=INT((DAY(A2)+WEEKDAY(EOMONTH(A2,-1)+1)-2)/7)+1.How to find the next upcoming date in a list in Excel
=MINIFS(B2:B7,B2:B7,">="&E1)Take the smallest date on or after today:
=MINIFS(B2:B7,B2:B7,">="&E1), with today in E1.How to calculate the average number of days between two dates in Excel
=AVERAGE(FILTER(C2:C8-B2:B8,C2:C8<>""))Subtract the columns and average the closed rows:
=AVERAGE(FILTER(C2:C8-B2:B8,C2:C8<>"")).How to convert an ISO 8601 timestamp to a date and time in Excel
=DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8))Convert the date and time parts and add them:
=DATEVALUE(LEFT(A2,10))+TIMEVALUE(MID(A2,12,8)).How to group invoices into aging buckets in Excel
=LET(days,$G$1-B2,IFS(days<=0,"Current",days<=30,"1-30",days<=60,"31-60",days<=90,"61-90",TRUE,"Over 90"))Work out the days past due once with LET, then pick the bucket with IFS:
=LET(days,$G$1-B2,IFS(days<=0,"Current",days<=30,"1-30",…,TRUE,"Over 90")).How to calculate how many days of a date range fall in each month in Excel
=MAX(0,MIN($B$2,EOMONTH(D2,0))-MAX($B$1,D2)+1)Overlap the range with each month:
=MAX(0,MIN($B$2,EOMONTH(D2,0))-MAX($B$1,D2)+1), with the month's first day in D2.How to prorate a monthly amount by days in Excel
=C2*(EOMONTH(B2,0)-B2+1)/DAY(EOMONTH(B2,0))Multiply by the share of the month used:
=C2*(EOMONTH(B2,0)-B2+1)/DAY(EOMONTH(B2,0)).How to calculate days between dates only when both dates are filled in Excel
=IF(OR(B2="",C2=""),"",C2-B2)Return empty text when either date is missing:
=IF(OR(B2="",C2=""),"",C2-B2).How to fix the #NUM! error in DATEDIF in Excel
=DATEDIF(MIN(B2,C2),MAX(B2,C2),"m")Put the dates in order with MIN and MAX:
=DATEDIF(MIN(B2,C2),MAX(B2,C2),"m"). DATEDIF returns #NUM! when the start is after the end.How to check if a date is later than another date in Excel
=IF(C2="","Open",IF(C2>B2,"Late","On time"))Compare the dates directly:
=IF(C2="","Open",IF(C2>B2,"Late","On time")). A later date is a larger number.How to filter rows from the last 30 days in Excel
=FILTER(A2:C9,(A2:A9>=F1-F2)*(A2:A9<=F1),"No rows")Keep the rows dated from today minus 30 to today:
=FILTER(A2:C9,(A2:A9>=F1-F2)*(A2:A9<=F1),"No rows").How to list biweekly pay dates in Excel
=SEQUENCE(B2,1,B1,14)Use SEQUENCE with a step of 14 days:
=SEQUENCE(B2,1,B1,14).How to check if a cell contains a date in Excel
=ISNUMBER(A2)Real dates are numbers, so
=ISNUMBER(A2)is TRUE for a date and FALSE for text that only looks like one.How to count orders by day of the week in Excel
=SUMPRODUCT(--(TEXT($B$2:$B$11,"ddd")=D2))Compare the day name of every date with SUMPRODUCT:
=SUMPRODUCT(--(TEXT($B$2:$B$11,"ddd")=D2)).How to calculate a due date with Net 30 end-of-month terms in Excel
=EOMONTH(B2,0)+$F$1Start the count at the end of the invoice month:
=EOMONTH(B2,0)+30.How to calculate days between dates for a whole column in Google Sheets
=ARRAYFORMULA(IF(C2:C8="","",C2:C8-B2:B8))Use one ARRAYFORMULA at the top:
=ARRAYFORMULA(IF(C2:C8="","",C2:C8-B2:B8)).How to add months to a month-end date and stay at the end of the month in Excel
=EOMONTH(A2,B2)Use
=EOMONTH(A2,B2). It adds the months and always returns the last day of the resulting month.How to calculate the number of overlapping days between two date ranges in Excel
=MAX(0,MIN(C2,$G$2)-MAX(B2,$G$1)+1)Take the later start and the earlier end:
=MAX(0,MIN(C2,$G$2)-MAX(B2,$G$1)+1).How to find the closest date to a given date in Excel
=XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),A2:A7)Find the smallest gap with MIN(ABS(…)) and look it up:
=XLOOKUP(MIN(ABS(A2:A7-E1)),ABS(A2:A7-E1),A2:A7).How to calculate the percentage of time elapsed between two dates in Excel
=MIN(1,MAX(0,($G$1-B2)/(C2-B2)))Divide the days so far by the total days and clamp to 0–100%:
=MIN(1,MAX(0,($G$1-B2)/(C2-B2))).How to find the first business day of the month in Excel
=WORKDAY(EOMONTH(A2,-1),1,$D$2:$D$3)Count one working day from the end of last month:
=WORKDAY(EOMONTH(A2,-1),1,holidays).How to extract a date from a text string in Excel
=DATEVALUE(MID(A2,SEARCH("20??-??-??",A2),10))Find the date pattern with SEARCH wildcards and convert it:
=DATEVALUE(MID(A2,SEARCH("20??-??-??",A2),10)).How to list semi-monthly pay dates (the 15th and last day) in Excel
=SORT(VSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0))))Stack the 15ths and the month ends and sort them:
=SORT(VSTACK(EDATE(B1,SEQUENCE(B2,1,0))+14,EOMONTH(B1,SEQUENCE(B2,1,0)))).How to get the start and end dates of last week in Excel
=A2-WEEKDAY(A2,3)-7Go back to this week's Monday and 7 more days:
=A2-WEEKDAY(A2,3)-7. Add 6 for last week's Sunday.How to calculate the sprint number for a date in Excel
=INT((A2-$E$1)/$E$2)+1Divide the days since the first sprint by the sprint length:
=INT((A2-$E$1)/$E$2)+1.How to calculate the fiscal month (period) number from a date in Excel
=MOD(MONTH(A2)-$E$1,12)+1Count the months since the fiscal year started:
=MOD(MONTH(A2)-$E$1,12)+1, with the start month in E1.How to find the earliest date in a list in Excel
=MIN(B2:B9)Use
=MIN(B2:B9). The earliest date is the smallest number; MIN skips blanks and text.How to convert quarter text like "Q1 2026" to a date in Excel
=DATE(RIGHT(A2,4),MID(A2,2,1)*3-2,1)Take the quarter number and the year and build the first day:
=DATE(RIGHT(A2,4),MID(A2,2,1)*3-2,1).How to calculate a project end date from a duration in working days in Excel
=WORKDAY(B2,C2-1,$F$2:$F$3)Count the duration minus one working day from the start:
=WORKDAY(B2,C2-1,holidays), so the start day counts as day 1.How to calculate the date someone turns a certain age in Excel
=EDATE(B2,12*$F$1)Move the birth date forward 12 months per year:
=EDATE(B2,12*$F$1), with the age in F1.