last working days

[#1]

if(len([Date Closed])<=0,now(),[Date Closed]) as DT2

Instead of Now() i want to find last working days of [date closed] month.

the date format is- 'mm/dd/yy hh:mi am/pm'

[Anonymous]

You can use like this.

Date(If(len([Date Closed]<0,today()-1,[Date Closed],'MM/DD/YY hh:mm TT')     AS DT2

this will fill the null value with previous day and rest will have Date closed.

Regards,

Imran K

[#3]

It is showing november date-2nd nov

[sunny_talwar]

Right, that make sense. Finally try this:

Date(FirstWorkDate(MonthEnd([Closed Date]), 1), 'MM/DD/YY hh:mm TT') as DT2

[#5]

Thanks for helping me. Apprecite it.

No its not helped me.

[swuehl]

No its not helped me.

I believe that's because we are just guessing around. If you want help, you need to detail on

- where we are starting from:

     Post some sample lines of your data records, e.g. an Excel table, INLINE script code or a small sample QVW.

- where we are heading to:

     Describe your requirements, i.e. the expected results, in more detail.

     Could be your data records, after transformation, attached to your post e.g. as Excel table.

I currently think we are missing both pieces of the puzzle.

[#7]

I have attached the QVW file. Please help.

[#8]

I have attached the file.Please help.

[Anonymous]

Hi Sesa,

Please elaborate on your requirement more.

As per your information it looks like,data for max month

in source  Oct/Nov is shown.

[#10]

Hi, maybe try this:

if(len([Date Closed])<=0,

Date(Floor(MonthEnd([Date Closed]),1)-if(WeekDay(MonthEnd([Date Closed]))>4,WeekDay(MonthEnd([Date Closed]))-4,0),'MM/DD/YY hh:mm TT'),[Date Closed]) as DT2

[swuehl]

Could you upload your input data table (the Excel file)?

It looks like the OPEN status records don't show [Date Closed] values in your input records, which is reasonable.

In your original post, you said:

"Instead of Now() i want to find last working days of [date closed] month."

Since the [date closed] is not set for those records, which month do you want to show then?

If I understood what you are going to do (calculating a duration between open and close, excluding weekends, holidays and non working hours) Now() seems to be reasonable for me to (without actually knowing your requirements ... see my last post why it's important to tell us more if you want help from the community).

You should be able to calculate the last working date of a month with above expressions, if needed.