11 Replies Latest reply on Oct 11, 2019 2:30 PM by Subodh Gupta

# Weekdays left in month from Today

Hi everyone,

Is there a formula to calculate WEEKDAYS left in the month from today.

I am able to get "days left to the month from today", with the following formula but it includes weekends. I don't want weekends.

Any help is truly appreciated.

Thanks!

Mansi

• ###### 1. Re: Weekdays left in month from Today

The following gives you weekend days between two days, I am sure you can retrofit it to figure out what you need. (If not, reply back here ....)

Reporting date can be Today and Transaction Date can be End of months which you already know how to find.

// Calculate Weekend days

INT((DATEDIFF('day',[Reporting Date],[Transaction Date])+ // Total Days

DATEPART('weekday',[Reporting Date])) /7 //How close to the next week is the reporting date (over/under compensate)

)

*2  //Times two because Sat/Sun

+ (if DATEPART('weekday',[Reporting Date]) = 1 then 1 else 0 end)// Add 1 because if reporting is a Sunday

- (if DATEPART('weekday',[Transaction Date]) = 7 then 1 else 0 end)// Subtract 1 if Transaction date is Sunday

• ###### 2. Re: Weekdays left in month from Today

Hi Mansi

If you have time could you please take the following quick survey to let us know about your Tableau Community Forums experience?

Many thanks

Ciara

[Program Manager | Tableau Community Forums]

• ###### 3. Re: Weekdays left in month from Today

Thank you for your response Subodh.

I am not getting the correct answer. Today can fall on any day when I refresh my dashboard and so can the last day fall on any day too (regarding where the comment says add 1 or subtract 1 if it falls on Sunday).

I would appreciate more help. Thank you very much!

INT((DATEDIFF('day', [Today],[Last Day of Month])+

DATEPART('weekday',[Today])) /7

)

*2

+ (if DATEPART('weekday',[Today]) = 1 then 1 else 0 end)

- (if DATEPART('weekday',[Last Day of Month]) = 7 then 1 else 0 end)

• ###### 4. Re: Weekdays left in month from Today

I found an answer. The below formula did the trick. Please feel free to use whoever needs it

DATEDIFF("weekday", [Today], [Last Day of Month])

- 2 * (DATEPART('week', [Last Day of Month]) - DATEPART('week', TODAY()))

+ (IF DATENAME('weekday',[Last Day of Month]) = 'Saturday' OR DATENAME('weekday', TODAY()) = 'Sunday'

THEN 0 ELSE 1 END) -1

1 of 1 people found this helpful
• ###### 5. Re: Weekdays left in month from Today

Mansi,

Is [Last Day of Month] here what you derived in your original post?

Subodh.

• ###### 6. Re: Weekdays left in month from Today

Yes! Its a part of it.

Last day of the month

Question: Now, I need the first day of the month. How could I edit the current formula?

Any help appreciated.

(I need to calculate how WEEK DAYS have ELAPSED from first day of the month to today. Basically reverse of everything I did till now)

Thank you

• ###### 7. Re: Weekdays left in month from Today

I was just writing the first day for something.

MAKEDATE(YEAR(TODAY()),MONTH(TODAY()),1)

• ###### 8. Re: Weekdays left in month from Today

Hi Subodh. Weird that Madedate was working yesterday and today it is giving me "Unknown Function" error.

I feel like I am missing something here. Any idea?

• ###### 9. Re: Weekdays left in month from Today

Makedate, note Madedate. I think that might be the issue, hope not. Or are you using it on a live connection? It might only work on an extract. !

• ###### 10. Re: Weekdays left in month from Today

I meant MakeDate. I am using a live connection. I do create extracts when I need to share a report. But I need a solution that will work always.

• ###### 11. Re: Weekdays left in month from Today

MAKEDATE() won't work on live connections. You will have to use a database/source-specific function. What is the source of your live connection?