# Date Calculations

Location:   http://www.mvps.org/dmcritchie/excel/datecalc.htm
[View without Frames]

 A B C D E Dates (Dates are shown in US/Canada format mm/dd/yyyy) Formula used starting date 01/19/1998 2 Mon =WEEKDAY(B3,1) add days 182 07/20/1998 Mon =B3+B4 add months 6 07/19/1998 Sun =DATE(YEAR(B3),MONTH(B3)+B5,DAY(B3)) add years 1 01/30/1999 Sat =DATE(YEAR(B3)+1,MONTH(B3),DAY(B4)) Age Calculation Q129277 XL: Undocumented Worksheet Function DATEDIF Anniv/Birthdate 06/01/1968 Example 24990 Years of Age 30 =DATEDIF(B9,NOW(),"Y") + Months 2 =DATEDIF(B9,NOW(),"YM") + Days 14 =DATEDIF(B9,NOW(),"MD") Todays Date 08/15/1998 =NOW() Days after Last Birthdate 75 =DATEDIF(B9,NOW(),"YD") More Date Calculations Day of Wk. Day of Wk. date chosen 03/15/1999 2 Mon 36234 =WEEKDAY(B18,1) =B18 First ddd of Month 03/01/1999 2 Mon =DATE(YEAR(B18),MONTH(B18),1) Last ddd of Month 03/31/1999 4 Wed =DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,1)-1 First Sunday of Mo. 03/07/1999 1 Sun =DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(1+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7) First Monday 03/01/1999 2 Mon =DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(2+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7) First Tuesday 03/02/1999 3 Tue =DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(3+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7) First Wednesday 03/03/1999 4 Wed =DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(4+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7) First Thursday 03/04/1999 5 Thu =DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(5+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7) First Friday 03/05/1999 6 Fri =DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(6+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7) First Saturday 03/06/1999 7 Sat =DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(7+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7) Last Sunday of Mo. 03/28/1999 1 Sun =DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,1)- WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,7)) Last Monday 03/29/1999 2 Mon =DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,1)- WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,6)) Last Tuesday 03/30/1999 3 Tue =DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,1)- WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,5)) Last Wednesday 03/31/1999 4 Wed =DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,1)- WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,4)) Last Thursday 03/25/1999 5 Thu =DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,1)- WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,3)) Last Friday 03/26/1999 6 Fri =DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,1)- WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,2)) Last Saturday 03/27/1999 7 Sat =DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,1)- WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18)+1,8)) =offset(cellname,-1,0) 27.03.1999 7 Sat =OFFSET(B37,-1,0) Last weeknum of yr 53 =WEEKNUM(DATE(YEAR(B18),12,31),1)
This page was intended as a summary of calculations on my datetime page, but some of the formulas shown here differ, I think the nth weekday day of Month formulas are easier to follow at First, Last, Nearest, Closest, nth weekday (4th Monday) in Month, Date calculations on my Date and Time page.
Chip Pearson has UDF on his pages to find the nth day of week for a month in a year see example then go to his page from there.
First Wednesday (4=Wed) of the month during month of 03/03/1999
=DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(4+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7)
add   7 to the above for the  2nd Wed
add 14 to the above for the  3rd Wed
add 21 to the above for the  4th Wed
the 5th Wed add 28 if it is still in same month it is valid
=IF(Month(\$B\$18)<>Month(formula+28),"",formula+28)

=IF(Month(\$B\$18)<>Month(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(4+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7)+28),"",DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)+ MOD(4+7-WEEKDAY(DATE(YEAR(\$B\$18),MONTH(\$B\$18),1)),7)+28)

January, 2002 has a 5th Wednesday and it is January 30, 2002.  Except in a Leap Year, February does not have a 5th Wednesday or any other day of week in the month.

## WORKDAY Worksheet Function

Formulas and Formats used to create table for the nth working day after the last day of previous month:
Workday is an Analysis Toolpak addin, simply activate it under Tools.
```=WORKDAY("10/01/2001"-1,ROW())     formatted as mm/dd/yyyy
=WORKDAY("11/01/2001"-1,ROW())
=WORKDAY("12/01/2001"-1,ROW())

=ROW()           formatted as General

=WEEKDAY(A1)     formatted as ddd
```
 A B C D E F G H I J K 1 10/01/2001 1 Mon 11/01/2001 1 Thu 12/03/2001 1 Mon 2 10/02/2001 2 Tue 11/02/2001 2 Fri 12/04/2001 2 Tue 3 10/03/2001 3 Wed 11/05/2001 3 Mon 12/05/2001 3 Wed 4 10/04/2001 4 Thu 11/06/2001 4 Tue 12/06/2001 4 Thu 5 10/05/2001 5 Fri 11/07/2001 5 Wed 12/07/2001 5 Fri 6 10/08/2001 6 Mon 11/08/2001 6 Thu 12/10/2001 6 Mon 7 10/09/2001 7 Tue 11/09/2001 7 Fri 12/11/2001 7 Tue 8 10/10/2001 8 Wed 11/12/2001 8 Mon 12/12/2001 8 Wed 9 10/11/2001 9 Thu 11/13/2001 9 Tue 12/13/2001 9 Thu 10 10/12/2001 10 Fri 11/14/2001 10 Wed 12/14/2001 10 Fri 11 10/15/2001 11 Mon 11/15/2001 11 Thu 12/17/2001 11 Mon 12 10/16/2001 12 Tue 11/16/2001 12 Fri 12/18/2001 12 Tue 13 10/17/2001 13 Wed 11/19/2001 13 Mon 12/19/2001 13 Wed 14 10/18/2001 14 Thu 11/20/2001 14 Tue 12/20/2001 14 Thu 15 10/19/2001 15 Fri 11/21/2001 15 Wed 12/21/2001 15 Fri 16 10/22/2001 16 Mon 11/22/2001 16 Thu 12/24/2001 16 Mon 17 10/23/2001 17 Tue 11/23/2001 17 Fri 12/25/2001 17 Tue 18 10/24/2001 18 Wed 11/26/2001 18 Mon 12/26/2001 18 Wed 19 10/25/2001 19 Thu 11/27/2001 19 Tue 12/27/2001 19 Thu 20 10/26/2001 20 Fri 11/28/2001 20 Wed 12/28/2001 20 Fri 21 10/29/2001 21 Mon 11/29/2001 21 Thu 12/31/2001 21 Mon 22 10/30/2001 22 Tue 11/30/2001 22 Fri 01/01/2002 22 Tue 23 10/31/2001 23 Wed 12/03/2001 23 Mon 01/02/2002 23 Wed

The dates above were chosen to include a month that begins on a Saturday (Dec 1, 2001), which is not a workday.

For illustrative purposes only.  Holidays were not included as a parameter in WORKDAY so Christmas (Dec 25), which is a US holiday is shown as a workday.
to work with holidays use   =WORKDAY(start_date,days,holidays)
i.e. =WORKDAY("12/01/2001"-1,20,{"12/24/01","12/25/2001"})

## First, 2nd, 3rd, 4th, 5th, named weekday, on or after a date

 A B C D E F G H I J 1 Date Weekday 1st-5thon or after calc. Date Day of week verify occur-rence Calc within Month 2 INPUT Calc. Reference INPUT Calculated Calc. Reference Calculated 3 11/1/2002 6 Fri 3 1 11/05/2002 3 1 11/05/2002 4 11/1/2002 6 Fri 3 2 11/12/2002 3 2 11/12/2002 5 11/1/2002 6 Fri 3 3 11/19/2002 3 3 11/19/2002 6 11/1/2002 6 Fri 3 4 11/26/2002 3 4 11/26/2002 7 11/1/2002 6 Fri 3 5 12/03/2002 3 1 -- 8 12/1/2002 1 Sun 3 1 12/03/2002 3 1 12/03/2002 9 12/1/2002 1 Sun 3 2 12/10/2002 3 2 12/10/2002 10 12/1/2002 1 Sun 3 3 12/17/2002 3 3 12/17/2002 11 12/1/2002 1 Sun 3 4 12/24/2002 3 4 12/24/2002 12 12/1/2002 1 Sun 3 5 12/31/2002 3 5 12/31/2002 13 14 12/2/2002 2 Mon 3 1 12/03/2002 3 1 12/03/2002 15 12/3/2002 3 Tue 3 1 12/03/2002 3 1 12/03/2002 16 12/4/2002 4 Wed 3 1 12/10/2002 3 2 12/10/2002 17 12/5/2002 5 Thu 3 1 12/10/2002 3 2 12/10/2002 18 12/6/2002 6 Fri 3 1 12/10/2002 3 2 12/10/2002 19 12/7/2002 7 Sat 3 1 12/10/2002 3 2 12/10/2002 20 12/8/2002 1 Sun 3 1 12/10/2002 3 2 12/10/2002 21 12/9/2002 2 Mon 3 1 12/10/2002 3 2 12/10/2002 22 12/14/2002 7 Sat 3 1 12/17/2002 3 3 12/17/2002 23 12/15/2002 1 Sun 3 1 12/17/2002 3 3 12/17/2002 24 12/16/2002 2 Mon 3 1 12/17/2002 3 3 12/17/2002 25 12/17/2002 3 Tue 3 2 12/24/2002 3 4 12/24/2002 26 12/18/2002 4 Wed 3 3 01/07/2003 3 1 -- 27 12/19/2002 5 Thu 3 3 01/07/2003 3 1 -- 28 Calculate the First, Third, Fifth Wednesday within the month 29 3/1/2003 4 1 03/05/2003 30 3/1/2003 4 3 03/19/2003 31 3/1/2003 4 5 -- 32 33 B3 =WEEKDAY(A3) 34 C3 =WEEKDAY(A3) 35 D3 3 36 E3 1 37 F3 =IF(WEEKDAY(A3)>D3,A3-WEEKDAY(A3)+D3+E3*7,A3-WEEKDAY(A3)+D3+E3*7-7) 38 G3 =WEEKDAY(F3) 39 H3 =INT((DAY(F3)+6)/7) 40 I3 41 J3 =IF(MONTH(IF(WEEKDAY(A3)>D3,A3-WEEKDAY(A3)+D3+E3*7,A3-WEEKDAY(A3)+D3+E3*7-7))<>MONTH(A3)," --",IF(WEEKDAY(A3)>D3,A3-WEEKDAY(A3)+D3+E3*7,A3-WEEKDAY(A3)+D3+E3*7-7) )

### Previous Friday   (#previous_fri)

Previous Friday
D1: =B1 -CHOOSE(WEEKDAY(B1,1),2,3,4,5,6,7,1)
D1: =B1-MOD(B1,7)-1     (simpler)

Last Friday of Month
F1: =DATE(YEAR(B1),MONTH(B1)+1,0) -CHOOSE(WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,0),1),2,3,4,5,6,0,1)

Friday before Last Day of Month
H1: =DATE(YEAR(B1),MONTH(B1)+1,0) -CHOOSE(WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,0),1),2,3,4,5,6,7,1)

#### Modify formula for other previous days   (each formula on same row is equivalent)

 J K L M N O P 1 2005-10-21 Fri J1: =B\$1-CHOOSE(WEEKDAY(B\$1,1),2,3,4,5,6,7,1) =B\$1-(WEEKDAY(B\$1+1)) =B\$1-MOD(B\$1+1,7) 2 2005-10-20 Thu J2: =B\$1-CHOOSE(WEEKDAY(B\$1,1),3,4,5,6,7,1,2) =B\$1-(WEEKDAY(B\$1+2)) =B\$1-MOD(B\$1+2,7) 3 2005-10-26 Wed J3: =B\$1-CHOOSE(WEEKDAY(B\$1,1),4,5,6,7,1,2,3) =B\$1-(WEEKDAY(B\$1+3)) =B\$1-MOD(B\$1+3,7) 4 2005-10-25 Tue J4: =B\$1-CHOOSE(WEEKDAY(B\$1,1),5,6,7,1,2,3,4) =B\$1-(WEEKDAY(B\$1+4)) =B\$1-MOD(B\$1+4,7) 5 2005-10-24 Mon J5: =B\$1-CHOOSE(WEEKDAY(B\$1,1),6,7,1,2,3,4,5) =B\$1-(WEEKDAY(B\$1+5)) =B\$1-MOD(B\$1+5,7) 6 2005-10-23 Sun J6: =B\$1-CHOOSE(WEEKDAY(B\$1,1),7,1,2,3,4,5,6) =B\$1-(WEEKDAY(B\$1+6)) =B\$1-MOD(B\$1+6,7) 7 2005-10-22 Sat J7: =B\$1-CHOOSE(WEEKDAY(B\$1,1),1,2,3,4,5,6,7) =B\$1-(WEEKDAY(B\$1)) =B\$1-MOD(B\$1,7)

#### More on Previuos Friday (#morefriday)

 B C D E F G H I J 1 Test Date Prev Fri Fri on or prev Fri on or after Fri after 2 2005-10-27 Thu 2005-10-21 Fri 2005-10-21 Fri 2005-10-28 Fri 2005-10-28 Fri 3 2005-10-28 Fri 2005-10-21 Fri 2005-10-28 Fri 2005-10-28 Fri 2005-11-04 Fri 4 2005-10-29 Sat 2005-10-28 Fri 2005-10-28 Fri 2005-11-04 Fri 2005-11-04 Fri 5 2005-10-30 Sun 2005-10-28 Fri 2005-10-28 Fri 2005-11-04 Fri 2005-11-04 Fri 6 2005-10-31 Mon 2005-10-28 Fri 2005-10-28 Fri 2005-11-04 Fri 2005-11-04 Fri 7 2005-11-01 Tue 2005-10-28 Fri 2005-10-28 Fri 2005-11-04 Fri 2005-11-04 Fri 8 2005-11-02 Wed 2005-10-28 Fri 2005-10-28 Fri 2005-11-04 Fri 2005-11-04 Fri 9 2005-11-03 Thu 2005-10-28 Fri 2005-10-28 Fri 2005-11-04 Fri 2005-11-04 Fri 10 2005-11-04 Fri 2005-10-28 Fri 2005-11-04 Fri 2005-11-04 Fri 2005-11-11 Fri 11 2005-11-05 Sat 2005-11-04 Fri 2005-11-04 Fri 2005-11-11 Fri 2005-11-11 Fri 12 2005-11-06 Sun 2005-11-04 Fri 2005-11-04 Fri 2005-11-11 Fri 2005-11-11 Fri 13 2005-11-07 Mon 2005-11-04 Fri 2005-11-04 Fri 2005-11-11 Fri 2005-11-11 Fri 14 2005-11-08 Tue 2005-11-04 Fri 2005-11-04 Fri 2005-11-11 Fri 2005-11-11 Fri 15 2005-11-09 Wed 2005-11-04 Fri 2005-11-04 Fri 2005-11-11 Fri 2005-11-11 Fri 16 2005-11-10 Thu 2005-11-04 Fri 2005-11-04 Fri 2005-11-11 Fri 2005-11-11 Fri 17 2005-11-11 Fri 2005-11-04 Fri 2005-11-11 Fri 2005-11-11 Fri 2005-11-18 Fri 18 2005-11-12 Sat 2005-11-11 Fri 2005-11-11 Fri 2005-11-18 Fri 2005-11-18 Fri 19 2005-11-13 Sun 2005-11-11 Fri 2005-11-11 Fri 2005-11-18 Fri 2005-11-18 Fri 20 2004-04-30 Fri 2004-04-23 Fri 2004-04-30 Fri 2004-04-30 Fri 2004-05-07 Fri 21 22 D2: =B2-MOD(B2,7)-1 23 F2: =B2-MOD(B2,7)-1+7*(MOD(B2,7)=6) 24 H2: =B2-MOD(B2,7)+6 25 J2: =B2-MOD(B2,7)-1+7*(MOD(B2,7)=6)+7 26 27 Test Date Prev Mon Mon on or prev Mon on or after Mon after 28 2005-10-27 Thu 2005-10-24 Mon 2005-10-24 Mon 2005-10-31 Mon 2005-10-31 Mon 29 2005-10-28 Fri 2005-10-24 Mon 2005-10-24 Mon 2005-10-31 Mon 2005-10-31 Mon 30 2005-10-29 Sat 2005-10-24 Mon 2005-10-24 Mon 2005-10-31 Mon 2005-10-31 Mon 31 2005-10-30 Sun 2005-10-24 Mon 2005-10-24 Mon 2005-10-31 Mon 2005-10-31 Mon 32 2005-10-31 Mon 2005-10-24 Mon 2005-10-31 Mon 2005-10-31 Mon 2005-11-07 Mon 33 2005-11-01 Tue 2005-10-31 Mon 2005-10-31 Mon 2005-11-07 Mon 2005-11-07 Mon 34 2005-11-02 Wed 2005-10-31 Mon 2005-10-31 Mon 2005-11-07 Mon 2005-11-07 Mon 35 2005-11-03 Thu 2005-10-31 Mon 2005-10-31 Mon 2005-11-07 Mon 2005-11-07 Mon 36 2005-11-04 Fri 2005-10-31 Mon 2005-10-31 Mon 2005-11-07 Mon 2005-11-07 Mon 37 2005-11-05 Sat 2005-10-31 Mon 2005-10-31 Mon 2005-11-07 Mon 2005-11-07 Mon 38 2005-11-06 Sun 2005-10-31 Mon 2005-10-31 Mon 2005-11-07 Mon 2005-11-07 Mon 39 2005-11-07 Mon 2005-10-31 Mon 2005-11-07 Mon 2005-11-07 Mon 2005-11-14 Mon 40 2005-11-08 Tue 2005-11-07 Mon 2005-11-07 Mon 2005-11-14 Mon 2005-11-14 Mon 41 2005-11-09 Wed 2005-11-07 Mon 2005-11-07 Mon 2005-11-14 Mon 2005-11-14 Mon 42 2005-11-10 Thu 2005-11-07 Mon 2005-11-07 Mon 2005-11-14 Mon 2005-11-14 Mon 43 2005-11-11 Fri 2005-11-07 Mon 2005-11-07 Mon 2005-11-14 Mon 2005-11-14 Mon 44 2005-11-12 Sat 2005-11-07 Mon 2005-11-07 Mon 2005-11-14 Mon 2005-11-14 Mon 45 2005-11-13 Sun 2005-11-07 Mon 2005-11-07 Mon 2005-11-14 Mon 2005-11-14 Mon 46 2004-04-30 Fri 2004-04-26 Mon 2004-04-26 Mon 2004-05-03 Mon 2004-05-03 Mon 47 48 D28: =B28-MOD(B28,7)+2 -(MOD(B28,7)<3)*7 49 F28: =B28-MOD(MOD(B28,7)-2,7) 50 H28: =B28-MOD(B28,7)+2 -(MOD(B28,7)<3)*7+7 51 J28: =B28-MOD(MOD(B28,7)-2,7)+7

## Related   (#related)

You are one of many distinguished visitors who have visited my site here or in a previous location  since opening on Aug 15, 1998.

Formatting for most of this page was accomplished with a program by Rob Bovey now at Application Professionals [Apps Pro], don't know which page you wouuld find it on: VBA Code Cleaner Dialog Converter VBA Code Documentor XY Chart Labeler,   Excel Utilities
Program is in Arhives at Baarns http://archive.baarns.com/EXCEL/PAGES/free.ASP
Of particular interest is the Excel to Web Table Converter (XL5 and up) which will convert selected cells to HTML and can include borders for empty cells.  Includes source code.  XL97 users especially will appreciate this one since MS dropped option choices in its converter (Save As).    If done today would use my own XL2HTML or to show row and column headers with the XL2HTMLx conversion.  See my page for more information on converting Excel to HTML and particularly in the Related area if not specifically covered in the main part of the page.

Return to David McRitchie's Excel Page. Return to Date & Time Page.  [Site Search -- Excel]

Updated on: 1998-08-15, 2002-09-04.

Please send your comments concerning this web page to: David McRitchie send email comments