 # Count specific weekdays between two dates / date range

I am trying to count the number of particular Weekdays between two dates. For example I want to know how many Sundays there were between DayX and Today().

I tried to use a formula like this: Countif(Weekday(Sequence([DayX],Today())),CurrentValue=1). However, looks like Weekday formula does not operate on a list, only on a specific date input. Do you have any suggestions how to run this so it works?

Bonus question: what equation would be if I wanted to count multiple types of weekdays (for example both Saturdays and Sundays). I think it should be Countif(Weekday(Sequence([DayX],Today())),CurrentValue=List(7,1)) but not sure about that since I cannot get Weekday formula to work on a list.

Thank you!

Hi @Vytenis !

I might have a little something for you there for both your questions Using this post as a base : Calculate all months in a range (20x faster) , I ended up with this formula to count Sundays (weekday n°1) in a date range :
(for some ease, I created a field `Today` in my test doc, but the formula works with `Today()` too and as `CountIf()` is a slow formula which can lead to performance problem, I didn’t use it )

`Sequence(thisRow.[Date X],thisRow.Today).FormulaMap(ToDate(CurrentValue).Weekday()).Filter(CurrentValue=1).Count()`

Therefore, for counting Saturdays and Sundays the formula is :
`Sequence(thisRow.[Date X],thisRow.Today).FormulaMap(ToDate(CurrentValue).Weekday()).Filter(CurrentValue=1 OR CurrentValue=7).Count()`

Which means that you can count any weekdays simply by changing the number of the `CurrentValue` in the `Filter()` (there’s an example with counting Mondays, Wednesdays and Fridays in the test doc )

The fields `Dates between Today & Date X [List]` and `Dates between Today & Date X : Weekdays [List]` are just there for visual help 1 Like

Excellet work, @Pch. Thanks a million. As it turns out, I needed to study the FormulaMap which I was not aware of (https://coda.io/formulas#FormulaMap). Since I am calculating this value in the text, then equation becomes rather simple. I have also modified it slightly with “Contains” filter that makes it easier to create a lookup list of week dates of interest. Sequence([First.Day],Today()).FormulaMap(CurrentValue.Weekday()).Filter(CurrentValue.Contains(2,3,4,5)).Count()

I did stumble upon a curious case, however by trying to check difference between two dates. Let’s take some date First.Day and get the duration between it and Today() by simply subtracting Today()-First.Day. We get duration X expressed as duration and written as “Days”. This X is less by one if I count Today(). So I try to add one day by adding Today()-First.Day+Days(1). And the result unfortunately gives me not duration anymore but Date format. That is quite puzzling. Here are the screenshots:

Any idea how to fix that?

You’re welcome !

I didn’t had enough info to get further in my exploration of your problem but as you seemed not that far from a working solution, I just thought that as a lead, it would be enough .
(Otherwise, I would continue to try )

For your second problem, could you try this :

`Today()-[First.Day]+Days(1).DateTimeTruncate("day")`

Excellent. If I understand correctly, the Days(1) would yield a DateTime value and not Duration. THANK YOU!

I must admit I’m not sure about why this is working But I’ve found another way to fix this too with this formula (which kind of “feels” more logical to me) :

`Days(ToDays(Today()-[First.Day])+Days(1))`

Edit @Vytenis :
There was a superfluous `ToDays()` there (I might be doing to many things at the same time … Sorry )

The corrected formula is :

`Days(Today()-[First.Day]+Days(1))` 1 Like

This is very clear - one format push onto entire equation! @Pch - if you are so kind to help, please have a look at couple more challenges I have posted. Any insight is truly appreciated!

1 Like

No worries , if I can help, I’ll do it 1 Like