Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

Number of specific dates or days between two dates

Hi,

I need a formula that can work out how many dates or days are between two dates, i.e. how many FRI between 1/1/2011 and 10/6/2011 or how many 21st's are there between the dates etc
0
StormFusion
Asked:
StormFusion
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
lisa_mcCommented:
hi

to find how many days eg monday or so on use

Number Of Mondays In Period

If you need to return the number of Monday's (or any other day) that occur within an interval between two dates, use the following Array Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)

examples from
http://www.cpearson.com/excel/datetimews.htm
0
 
lisa_mcCommented:
and the link below will help you out with the second problem

http://www.cpearson.com/excel/DateIntervals.htm
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

to work out the number of days between to dates you simply need to subtract the earlier date from the later date

Say A1 is 1/01/2011 and A2 is 1/04/2011 (April-1-2011)

=A2-A1

will return 90 (days).

As a rough guideline, you could divide that result by 7 too arrive at the number of Fridays or any other weekday as an average.

Your also ask about how many 21sts there are. This seems like quite a few requirements. What exactly do you want to return?

Can you post a workbook with some data and manually mocked up results?

cheers, teylyn
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
StormFusionAuthor Commented:
The first reply above works perfect for working out certain days between two dates, thats fine.

But I also need to work out for example... 1/1/2011 to 27//4/2011 and looking for 28th's, would give me a result of 3, because it would see 28/1/2011, 28/2/2011 and 28/3/2011 but not 28/4/2011 as the to date finishes on the 27th.
0
 
Patrick MatthewsCommented:
Same basic approach should work to count the number of indicated days of the month:

Assuming that your day of the month is in D2...

{=SUM(IF(DAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=D2,1,0))}

As before, that is an array formula.  To enter an array formula, do not enter the curly braces, and hit Ctrl+Shift+Enter instead of Enter to finish it off.  Excel will then display those braces to indicate that it's an array formula.
0
 
StormFusionAuthor Commented:
Lisa_mc thankyou for those, and mathewpartrick thanks, tried to change it to also count number of certain months between date by changing DAY to MONTH but didnt work?
0
 
Patrick MatthewsCommented:
StormFusion,

With respect, I think your original question has been answered.  If you want to tackle that months part, I suggest you create a new question for it.

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now