Generating the Custom Date based on the Work Week of the calendar

[lakshmanvepuri]

Hi ,

I will receive the file alternate week and based on that i need to generate the below two columns.

Suppose I receive File on 10-June-2020... I should Display 

vVersion ---  WW24_2020

vDate -------  18-JUNE-2020 

Please check the attached file for the calendar.

Thanks.

Vepuri

[vamsee]

Assuming your file name would have a date, in it. (You can use Filebasename)

Try

'WW'&Week(FileBasename())&'_' &Year(FileBasename()) as Column1,
Date(WeekEnd(FileBasename())+5, 'DD-MMMM-YYYY') as Column2,

If you want only the Thursday's highlighted in Yellow to be used in Column2, you might've to get a list of dates for each year.

I don't think an excel in that format would be useful.

[lakshmanvepuri]

HI @vamsee 

The File Name will be like this..

Eg:

M05_WW22_DC_May17_MasterData_Up.xlsx

M06_WW24_DC_Jun17_MasterData_Up.xlsx

M06_WW26_DC_Jun17_MasterData_Up.xlsx

[vamsee · Accepted solution]

Hi,

I have defaulted the year to be 2020.

Try

Column1:

'WW'&Week(MakeDate('2020', Month(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD')), Day(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD'))))&'_' &

Year(MakeDate('2020', Month(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD')), Day(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD'))))

Column2:

Date(WeekEnd(MakeDate('2020', Month(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD')), Day(Date#(Subfield(Filebasename(),'_', 4), 'MMMDD'))))+5, 'DD-MMMM-YYYY')

 For Column 1 you can also use the Filename like SubField(Filebasename(), '_',2)