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 :slight_smile: !

I might have a little something for you there for both your questions :slight_smile:

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 :wink: and as CountIf() is a slow formula which can lead to performance problem, I didn’t use it :wink: )

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() :slight_smile: (there’s an example with counting Mondays, Wednesdays and Fridays in the test doc :wink: )

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

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 :wink: !

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 :wink: .
(Otherwise, I would continue to try :wink: )

For your second problem, could you try this :slight_smile: :

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 :thinking:

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 :sweat_smile: (I might be doing to many things at the same time :sweat_smile: … Sorry :blush: )

The corrected formula is :

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

:slight_smile:

1 Like

This is very clear - one format push onto entire equation! :wink: @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 :wink: , if I can help, I’ll do it :wink:

1 Like