I need a formula to display how many of each weekday there have been to date in 2015. For example, as of today, April 17th, there have been 15 Mondays, Tuesdays, Wednesdays and Thursdays, and 16 Fridays. This would need to automatically update every day, and starting in 2016 would start all over again. Ideally, legal holidays would be excluded. I suspect some derivation of NETWORKDAYS could be used, but I couldn’t figure out the syntax.
Thank you.
how bout this:
Formula:
Please Login or Register to view this content.
copied down for 5 rows with 1 through 5 in column C?
edit*** that formula is confused by the fact that we started the year on a thursday... working on it.
edit 2*** you could just put 3, 4, 5, 1, 2 in column C as a quick and dirty fix... will last you through 2015 anyway, at which point you'll need to update the 42005 in this formula anyways. all that could be automated but the formula gets a little uglier... let me know if you want it.
Last edited by simarui; 04-17-2015 at 12:14 PM. Reason: formula's bogus...
Hope I could help - if your post has been solved don't forget to mark it as such.
Clicking the * below is a great way to say thanks!
"Drowning in 10 feet of water isn?t any different than drowning in a million feet. And if you can swim, it doesn?t matter how deep the ocean is. At some level, once you realize you?re in water that?s too deep to stand, you have to have a very different approach," - Joi Ito
Formula:Please Login or Register to view this content.
Formula gives you the number of Mondays from the first day of current year until today. Change the red part of the formula for the other days.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Thank you, simarui and Fotis, both of these work well except they're both including January 1st, which is a legal holiday in the US (not a workday). Is there any way to get these formulae to not include legal holidays?
you can add holidays to be excluded to the networkdays formula by listing your holidays in a range and adding that range as the third parameter
like this:
Formula:Please Login or Register to view this content.
where my list of holidays is in M1:M5
Thank you, simarui, I was able to get your suggestion to work. The numbers I needed to use after C were 4, 5, 6, 1 and 2. This now does exactly what I need.
Thanks again.
ah, good call - 3, 4, 5, 1, 2 was still assuming the year started on thursday... but since thursday is a holiday the year starts on friday.
anyway, glad i could help!
In Excel 2010 or later you can use NETWORKDAYS.INTL function to do this more simply, e.g. with C1:C5 showing 1,2,3,4,5 in that order you can use this formula in row 1 copied down and it will work for any date in any year
=NETWORKDAYS.INTL(DATE(YEAR(A$1),1,1),A$1,SUBSTITUTE(1111111,1,0,C1),H$2:H$10)
Assumes today's date in A1 and holidays listed in H2:H10
In earlier versions of excel this formula will give you the same result
=INT((A$1-WEEKDAY(A$1-C1)-DATE(YEAR(A$1),1,-7))/7)-SUMPRODUCT((WEEKDAY(H$2:H$10,2)=C1)*(YEAR(H$2:H$10)=YEAR(A$1))*(H$2:H$10<=A$1))
Audere est facere
Even better - thanks very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks