Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'
Is max([Date Closed]) what you're looking for?
May be this:
=WeekStart(MonthEnd([Date Closed]), 5)
Last working day of the month:
If(Len([Date Closed]) <= 0, FirstWorkDate(MonthEnd([Date Closed]), 1), [Date Closed]) as DT2
Isn't [Date Closed] unknown for those records where the condition is true?
Which Month / Last Working Date do you want to use then?
Maybe
FirstWorkDate(MonthEnd(Today()), 1)
Too true
Maybe this instead
If(Len([Date Closed]) = 0, FirstWorkDate(MonthEnd(Today()), 1), [Date Closed]) as DT2
I followed your suggestion and the date is converted into number and I have tried vice versa but it didn't('mm/dd/yy hh:mi am/pm').
May be try this:
Date(If(Len([Date Closed]) = 0, FirstWorkDate(MonthEnd(Today()), 1), [Date Closed]), 'MM/DD/YY hh:mm TT') as DT2
Almost reaching.
But-
If we have august data then it shpould come 31st of aug and sep -30th sep.
But it is showing oct -30th
may be we have used today() but inplace of today() if we use Date Closed then it is giving us different result.
The problem with using [Date Closed] is that you are saying that if Len([Date Closed]) = 0 (meaning it is empty) then do the first thing which give you null() or 0 as well.
Have you tried this:
Date(WeekStart(MonthEnd([Date Closed]), 5), 'MM/DD/YY hh:mm TT') as DT2