4 Replies Latest reply on Oct 29, 2018 12:57 PM by Taylor Wilson

# Count records with "Created Date" 2 or more weekdays in the past

Hi,

I'm struggling to create a calculated field that would count only the records where Created Date is 2 or more weekdays greater than today within a month. So for example, if my set looked like this...

Records
Created Date

Record 1

10/19/2018
Record 210/5/2018
Record 3

10/13/2018

Record 410/21/2018

In this instance and looking at today's view of 10/22, it would only count records 2 and 3. Since 10/20 is Saturday and 10/21 is Sunday, records 1 and 4 would not meet criteria.

Can anyone provide a solution?

Thanks!

• ###### 1. Re: Count records with "Created Date" 2 or more weekdays in the past

Hello Taylor,

You can try below steps:

current month flag

DATETRUNC('month',TODAY())=DATETRUNC('month',[Created Date])

2 weekdays?

DATEDIFF('day',[Created Date],TODAY())

day name

DATENAME('weekday',[Created Date])

Flag

[current month flag]=TRUE AND [2 weekdays]>=2 and  ([day name]<>'Saturday' AND [day name]<>'Sunday')

You can use "Flag" on filter shelf and select "TRUE".

• ###### 2. Re: Count records with "Created Date" 2 or more weekdays in the past

Hi Meenu,

Thanks for your response. In my instance, I need Record 3 to be true because although it's created on a Weekend Day it would still be counted because there has been at least 2 weekdays between the date and today's date.

Your "2 weekdays?" calculation is the most useful but I need to incorporate logic that counts only weekdays and this seems to count all days.

Anything you can add to that specific calculation that would result in ex. Record 1 = 1, Record 2 = 11, Record 3 = 6, record 4 = 1?

• ###### 3. Re: Count records with "Created Date" 2 or more weekdays in the past

Can anyone help here? The calculation I am using is not working for me.

Again - I'm trying to create a calculation that will only count records with a Created Date equal to or greater than 2 weekdays in the past. So for instance, today is Monday, Oct 29 - only records created on Thursday, Oct 25 or before this date would be included.

• ###### 4. Re: Count records with "Created Date" 2 or more weekdays in the past

NVM I figured it out on my own. In case anyone is interested, see below.

IF ((DATEDIFF('day',[Created Date],TODAY()) -

(7-DATEPART('weekday',[Created Date])) -

DATEPART('weekday',TODAY())

) / 7*5

+ MIN(5,(7-(DATEPART('weekday',[Created Date]))))

+ MIN(5,(DATEPART('weekday',TODAY())-1))

-1) >= 2 THEN 1 ELSE 0 END

Then Sum the field