| 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) | ||
=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.
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"})
Formatting for most of this page was accomplished using: Free Excel Downloads from Baarns Consulting -- (Rob Bovey) Microsoft Office Resource Center. 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.
Return to David McRitchie's Excel Page. Return to Date & Time Page. [Site Search -- Excel]
Updated on: 8/15/1998, 09/04/2002.
For comments or feedback contact: David McRitchie.