Date to date

Date functions in Excel can answer a whole plethora of questions we are dealing with in everyday life. Some of them are quite straightforward, and some not quite, but all have practical applications. 

Couple of notes, before you try to use any of the formulas:

• MONDAY is designated here as the first day of the week
  
• Cells with the formulas shown here must be in General format, not Date format.
• Cells A2:C2 are presented in Custom format: "dddd dd/mm/yyyy"

Useful tip: you can enter Current date using this shortcut: CTRL+; (semicolon)

Formulas presented here, when supported with a simple setup in a worksheet, like this one:

will help you to find many of your questions answered. Here are some examples:

➤ How many days left in this calendar year:    =DATE(YEAR(A2),12,31)-A2

➤ Number of specific week days between two dates, the second date inclusive. Use the following formulas for the number of:

• Mondays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=1)*1)
• Tuesdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=2)*1)
• Wednesdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=3)*1)
• Thursdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=4)*1)
• Fridays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=5)*1) 
• Saturdays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=6)*1) 
• Sundays:    =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)=7)*1) 

➤ Number of weekdays (Monday-Friday) between two dates, the second date inclusive:

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)={1,2,3,4,5})*1)

➤ Number of weekend days (Saturday-Sunday) between two dates, the second date inclusive: 

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)={6,7})*1)

➤ Number of business days, with holidays excluded:

=NETWORKDAYS.INTL(A2,B2,1,Freedays), where 'Freedays' represents a range of holiday dates set up and named so in your worksheet (if needed).

➤ Number of days survived since somebody's birthday (entered in cell C2):

 =SUMPRODUCT((WEEKDAY(ROW(INDIRECT(C2&":"&A2)),2)={1,2,3,4,5,6,7})*1)-1

➤ Number of days left till the end of this century (including 31 Dec, 2100):

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(TODAY()&":"&DATE(2100,12,31))),2)={1,2,3,4,5,6,7})*1)