Default selection in date wit specific condition

[nehasaxena]

Hi Members,

I have to implement 1 scenario - Select date of previous month end when activate sheet.  This is easily achievable but the complexity is for the work day 1 and work day 2 select previous to previous month end.  For rest of the days its previous month end.

I have 1 qvd it has work day info with dates.  I am not able to figure out how do i use this work day table to handle the selections for work day 1 and 2.

If someone has any idea please do share. It would be great help for me. Thanks

Regards

Neha

[sunny_talwar]

@nehasaxena would you be able to share some sample data and explain what are you looking to get from the sample you share

[nehasaxena]

 I am adding more information. 2 Excel files attached, 1 file has the dates that are in my main data. Records are available only for these dates. 

2nd file is which has workday information.

I want to achieve soemthing like this  - =If(match(workday,'WD1','WD2'),Date(MonthEnd(AddMonths(today(),-2)),'M/D/YYYY'),Date(MonthEnd(AddMonths(today(),-1)),'M/D/YYYY'))

This i want trigger when specific sheet open. But we have select in field trigger, not sure this kind of expression will work. 

Hope this helps. Please do suggest.

[nehasaxena]

Hi Sunny, I have replied in original post please do check the files.

[juanma21]

More or less something like that, filters can help you.

SCRIPT:

WorkDay:
LOAD WorkDay,
Date,
monthend(Date) as Month_End
FROM [C:\Users\Juan\Desktop\Tablas\WorkDay.xlsx]
(ooxml, embedded labels, table is Sheet1);

[juanma21]

in the script you can use the weeks

SCRIPT:

WorkDay:
LOAD WorkDay,
Date,
Monthend(Date) as Month_End,
Week(Date) as WeekDay
FROM [C:\Users\Juan\Desktop\Tablas\WorkDay.xlsx]
(ooxml, embedded labels, table is Sheet1);

[juanma21]

I hope I have helped you in something, if it is not the case, please give me more details
Greetings

Juan

[Brett_Bleess]

@sunny_talwar Since I think notifications are still not working as they used to, just wanted to flag this one, as poster did respond back and attach some data files, another poster did reply, but we have no close-out by the poster either, so if you can have a look if you have time, much appreciated.

Regards,
Brett