Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have a date column like below(It has 14 days data:
1/1/2020
1/2/2020
1/3/2020
1/4/2020
1/5/2020
1/6/2020
1/7/2020
1/8/2020
1/9/2020
1/10/2020
1/11/2020
1/12/2020
1/13/2020
1/14/2020
1/15/2020
1/16/2020
1/17/2020
1/18/2020
1/19/2020
1/20/2020
1/21/2020
1/22/2020
1/23/2020
1/24/2020
1/25/2020
1/26/2020
1/27/2020
1/28/2020
I want an output like this:
Date New_Date
1/1/2020 1/14/2020
1/2/2020 1/14/2020
1/3/2020 1/14/2020
1/4/2020 1/14/2020
1/5/2020 1/14/2020
1/6/2020 1/14/2020
1/7/2020 1/14/2020
1/8/2020 1/14/2020
1/9/2020 1/14/2020
1/10/2020 1/14/2020
1/11/2020 1/14/2020
1/12/2020 1/14/2020
1/13/2020 1/14/2020
1/14/2020 1/28/2020
1/15/2020 1/28/2020
1/16/2020 1/28/2020
1/17/2020 1/28/2020
1/18/2020 1/28/2020
1/19/2020 1/28/2020
1/20/2020 1/28/2020
1/21/2020 1/28/2020
1/22/2020 1/28/2020
1/23/2020 1/28/2020
1/24/2020 1/28/2020
1/25/2020 1/28/2020
1/26/2020 1/28/2020
1/27/2020 1/28/2020
Starting at 1/1/2020, i need to show same date till two weeks(14 Daya) in second row, and repeat same for rest data. So i need 14 days date in second column.
New_Date = Date((Num([Date]) + 13 - Mod(Num([Date]) - Num(Date#('1/1/2020')),14)),'M/D/YYYY')
New_Date = Date((Num([Date]) + 13 - Mod(Num([Date]) - Num(Date#('1/1/2020')),14)),'M/D/YYYY')
Thank you. It worked.
Could you please explain this code in a bit more detail?
The Mod() function returns the remainder of a number1/number2. The Mod(Num(Date) - Num(Date#('1/1/202/)),14) creates a repeating series of numbers from 0 to 13. I take the current date and add 13 - the Mod() result which is 0 for the base date to give me a result 14 days in the future from the base date. I then move 1 day into the future and add 13 and - the Mod() which is now 1, the result is still 14 days in the future from the base date. See the example below.
This is impressive. Appreciate your help. 🙂