I'm building a budget model, and I need to figure out a way (for a more accurate budget) how to count the number of fridays (or thursdays, etc.)
Ok, so the assumptions:
in A1 is the day i need to count: Friday
in B1 is the Month/Year : Jan - 09
in C1 is the next Month/Year: Feb - 09
etc.
There are 5 Friday in Jan and 4 in Feb.
What would a good formula for this be?
Thanks!
Last edited by robert_shindorf; 01-26-2009 at 08:50 PM.
See attached,
A2:A3 contain numbers 1 to 7 representing Sunday to Saturday, these are formatted as dddd to show the relevant day of the week.
Formula in B2 copied across and down
=4+(DAY(B$1+34)<WEEKDAY(B$1-$A2))
perfect!
as a secondary question... what's the best way to have a formula to add 1 month across the top?
you can do =b1+30, but you'll have issues... is there a month()+1 or something?
also, will you explain that formula to me? i don't understand why you have a +34, etc.
If B1 has the 1st of the month and you want each successive 1st of the month then you don't even need a formula, you can just drag B1 across and it should increment them automatically......or with a formula
=EDATE(B1,1)
which uses Analysis ToolPak add-in function or, avoiding that
=DATE(YEAR(B1),MONTH(B1)+1,DAY(B1))
or (only works if dates are the 1st of the month)
=B1+32-DAY(B1+31)
awesome... sometime there are fiscal years instead of a calender year, so a formula is required for certain functions!
Seriously cool formula.
Thanks daddylonglegs.
Cheers
the formula works magicexcuse my ignorance though but what is the function of the " < " in the above formula "=4+(DAY(B$1+34)<WEEKDAY(B$1-$A2))"
It's the "less than" symbol.
It's testing that DAY(B$1+34) is less than WEEKDAY(B$1-$A2).
If that condition is TRUE then it adds 1 to 4.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks