Count specific weekdays between two dates / date range

[Vytenis]

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!

[Pch]

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

[Vytenis]

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?

[Pch]

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")

[Vytenis]

@Pch

Days(1).DateTimeTruncate(“day”)

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

[Pch]

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))

[Vytenis]

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!

[Pch]

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