Excel - Counting specific days within a date range 1

[jonfurn]

Hi -

I wonder if anyone could help me.

I'm just trying to do some calculations on someone's sick days. I've used NETWORKDAYS ok to add them all up.

However, this particular lady suddenly starts to work only on a Tues, Weds and Thurs.

How can I count how many Tuesdays (e.g.) in a date range (i.e. 26/9/03 - 18/11/03)??

Any help would be very much appreciated as I am losing hair fast!!!

Thanks everyone.
Jonfurn

 

[SkipVought]

Hi,
[tt]
=SUMPRODUCT((MOD($C$3:$C$39,7)=3)*(1))
[/tt]


Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[SkipVought]

Hi,
[tt]
=SUMPRODUCT((MOD($C$3:$C$39,7)=3)*(1))
[/tt]
substitute your data range

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[jonfurn]

Thanks for your response, Skip.

I have a 'Start' date in Column A and an 'End' date in Column B. In this particular example, the dates I used in my first post are in row 17. I've substitued this range into the formula as such:

=SUMPRODUCT((MOD(A17:B17,7)=3)*(1))

However, I'm getting the answer "1" to this, where I would expect it to be "8" (NETWORKDAYS is giving me "38")

Sorry to be a pain!
Thanks for your help.
J

 

[SkipVought]

You ONLY have 2 cells referenced! The MAX you could get is 2!

If you need to sum on 2 COLUMNS
[tt]
=SUMPRODUCT((MOD(A1:A17,7)=3)*(1))+SUMPRODUCT((MOD(B1:B17,7)=3)*(1))
[/tt]


Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[jonfurn]

Sorry Skip -

I'm still a bit confused (easily done I'm afraid!).

In A17 I have 26/09/2003 (the start of sickness). In B17 I have 18/11/2003 (the end of sickness). This formula doesn't seem to count the number of (for instance) Tuesdays within that range.

Sorry..... really appreciate your help (and patience!!)

J

 

[SkipVought]

Ahhhhh,

You are using RANGE in a different way than I am using range.

I was referring to a RANGE of CELLS -- you were referring to a RANGE of DATES. BIG DIFFERENCE!

1) Make a list of dates, starting whenever you want, like 1/1/2003 -- copy down 365 cell and you have a year of dates (takes about 6 seconds) make it a 10 year list if you want.

2) in the column adjacent to the dates, enter this formula and copy down
[tt]
=MOD(A1,7)=DayOfWeek
[/tt]
where DayOfWeek is 3 for Tuesday

3) assuming your date list is on sheet3!A1:A365, new formula
[tt]
=SUMPRODUCT((Sheet3!A1:A365>=Sheet1!A17)*(Sheet3!A1:A365<=Sheet1!B17)*(Sheet3!B1:B365))
[/tt]




Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[jonfurn]

Many thanks for your speedy help, Skip - that worked great.

I'm not sure I'd have come up with that (even if I'd had months to sit and think about it!!)

Very much appreciated.

All the best,
J

 

[PeanutB7]

Skip,

I am looking to do the same calculation but within an Access Table. With a parmater query I am having the reviewer select a date range. I would like Access to view each project record and state how many days a project falls within that range selected an populate an additional column with that answer. If the project starts and ends before or after the selected date range than naturally the additional column reads zero. If it starts befor but ends during than it calculates the days within the selected range and so on. Is ther a query and / or SQL language that would acomplish my goal Please Please Please.

Thank you in advance!

JB

 

[CautionMP]

JB,
In Access the closest function is DateDiff(). Your request has a lot of moving parts so you may need to create your own function around the DateDiff function to do the calculation based on the project start/stop dates and the parameter start/stop dates.

Public Function ProjectDaysInRange(ProjectStart As Date, _
  ProjectStop As Date, UserSelectStart As Date, _
  UserSelectStop As Date) As Long
  Dim lngOutput As Long
  If ProjectStart > UserSelectStop Or _
    ProjectStop < UserSelectStart Then
      lngOutput = 0
  ElseIf ProjectStart >= UserSelectStart And _
    ProjectStop <= UserSelectStop Then
      lngOutput = DateDiff("d", ProjectStart, ProjectStop)
  ElseIf ProjectStart <= UserSelectStart And _
    ProjectStop >= UserSelectStop Then
      lngOutput = DateDiff("d", UserSelectStart, UserSelectStop)
  ElseIf ProjectStart <= UserSelectStart And _
    ProjectStop <= UserSelectStop Then
      lngOutput = DateDiff("d", UserSelectStart, ProjectStop)
  ElseIf ProjectStart >= UserSelectStart And _
    ProjectStop >= UserSelectStop Then
      lngOutput = DateDiff("d", ProjectStart, UserSelectStop)
  End If
  ProjectDaysInRange = lngOutput
End Function

Paste the abpve into a regular code module, you can then call it in your query as follows:
[tt]ProjectDays: ProjectDaysInRange([pStart], [pStop], paramStart, paramStop)[/tt]
Where [ul square][li]pStart is the start date from the current record[/li]
[li]pStop is the stop date from the current record[/li]
[li]paramStart is the user selected start date[/li]
[li]paramStop is the user selected stop date[/li]
[/ul]

Hope this helps,
CMP
[sub]Just because something can be done doesn't make it a good idea...
...but how will you know 'till you try[/sub]