Excel MT142 p2: Array Formula for Counting Dates
Loading...
Uploaded by ExcelIsFun on Oct 28, 2008
See how to create a complex array formula that counts weekends between two dates with the SUM IF WEEKDAY ROW and INDIRECT functions in one formula
See how to create an array formula that counts with date and other criteria
Also see: From a beginning and ending date, see how to create a list of dates (serial numbers) in memory using the ROW and INDIRECT functions. Also see this video for another example of this date trick: Excel Magic Trick # 165: MACRO to Extract Birthdays.
- 15 likes, 3 dislikes
-
Uploader Comments (ExcelIsFun)
All Comments (8)
-
Typung error!
It should Be weekday - (greater) 6
-
I am glad that the video helped. But there are so many ways to do things in Excel, that I weary of saying "wrong". You never know, COLUMN probably would work fine - you should try it!! (I am on vacation without a computer that has Excel or else I would try it...)!
-
The usage of ROW and INDIRECT function to generate an array of dates is awesome. As an Excel user I was inclined to use COLUMN function instead of ROW, but obviously I am wrong. Once again an old but awesome video.
- 9:52
Excel MT142 p1: Custom Number Format & Conditional Formatting for Dates & Weekendsby ExcelIsFun5,549 views
- 6:03
Excel Magic Trick #151: DATEDIF function (between two dates)by ExcelIsFun20,578 views
- 10:36
Excel Magic Trick 489: Extract Only Numbers From Text String Array Formulaby ExcelIsFun8,853 views
- 10:33
Excel MT 144 p1 Formula: Avoid Data Validation 1 Rule Limitby ExcelIsFun4,141 views
- 9:29
Excel Magic Trick #147: Dynamic List from Table #2by ExcelIsFun5,649 views
- 4:44
Calculate the Days Worked on a Project in Excelby DannyRocksExcels3,687 views
- 11:06
Excel Magic Trick 608: Array Formula To Return Multiple Items - List Students With Free Periodsby ExcelIsFun6,619 views
- 4:42
Excel MT #143: VBA: Avoid Data Validation 1 Rule Limitby ExcelIsFun5,446 views
- 5:00
Excel Magic Trick 372: WORKDAY function - Find Date in Futureby ExcelIsFun6,944 views
- 10:31
Excel Magic Trick 167p1 IF function formula: 12 Examples!by ExcelIsFun168,254 views
- 3:45
Excel Magic Trick #12: Date Math!by ExcelIsFun66,652 views
- 7:12
How to Calculate a Future Date in Excel 2003by DannyRocksExcels8,269 views
- 5:34
Excel Magic Trick #189: Count Days Not Sundayby ExcelIsFun4,589 views
- 10:34
Excel Magic Trick 167p2 IF function formula: 12 Examples!by ExcelIsFun29,229 views
- 3:52
Office 2010 Class #30: Excel Date Formulas: Days Invoice Past Due, Loan Maturity Date, Project Daysby ExcelIsFun7,299 views
- 12:45
Excel Magic Trick 336: Incrementing Numbers In Formulasby ExcelIsFun6,370 views
- 6:39
Excel Magic Trick #175: Count Between Upper & Lower Valueby ExcelIsFun4,902 views
- 8:08
Excel Magic Trick # 156: List Of Unique Items w Formula From 2 Listsby ExcelIsFun7,576 views
- 9:41
Excel Basics #22: IF function formula made easy!by ExcelIsFun128,574 views
- 7:07
Excel Magic Trick #133: Import CSV Data (Comma Separated Values - Data)by ExcelIsFun16,035 views
You're right! That was my formula, too. I forgot to post the = sign! Weekday greater or eqaul 6.
Merrell2780 1 year ago
Thanks!!
ExcelIsFun 1 year ago
Hi,
You could also use weekday-6 instead of using the weekday Part of the formula twice with 6 and 7 for the weekend. Is there a reason to do so?
Your Videos are great I like them. :)
Merrell2780 1 year ago
Absolutely!!
A better formula for "count weekends" would be:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(C15&":"&D15)),2)>5))
A better formula for "Count do":
=SUMPRODUCT(--(E$14:AI$14>=$C15),--(E$14:AI$14<=$D15),--(E15:AI15="do"))
ExcelIsFun 1 year ago
can you show me a video about of a formula on how to create debt reduction and how long does it take
apsjr1 1 year ago
Try these:
Excel Finance Class 42: Consumer Amortized Loans
Excel Finance Class 39: How Long To Pay Off Credit Card With Minimum Payment
ExcelIsFun 1 year ago