You've created a table which contains a column with dates. Now you need to do some calculations on numeric data included between some two selected dates. E.g., you want to calculate average of some 'Output' numbers falling within one month, December 2021, like in this table:
If your table is sorted by the 'Date' column, the simplest way to get your average 'Output' is as follows:
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:
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:
➤ 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)
