Assistance Needed for Transforming `Period` to `Date` Column

[WassimAB]

Hi,  

I need help transforming a `Period` column into a corresponding `Date` column based on the logic provided in the attached example. Here's the scenario:  

 

1. The `Period` column contains values like `X1`, `X2`, `X3`, etc., where `X` represents today's date (in this case, 11-Dec-2024), and the number after `X` indicates the number of days to subtract from today's date.  

2. The goal is to generate the `Date` column that represents the exact date after applying the subtraction logic. For example:  

   - `X1` = Today's Date - 1 day = `10-Dec-2024`  

   - `X2` = Today's Date - 2 days = `09-Dec-2024`  

**Attached Example Output:**  

 

The attached image illustrates the expected output for reference. 

Thanks in advance!  

Best regards

[Kaustubh17 · Accepted solution]

Hi @WassimAB 

Please use the below formula for the results.

DateTimeFormat(DateTimeAdd(DateTimeToday(), -ToNumber(Replace([Period], 'X', '')), 'days'), '%Y-%m-%d')

Please let me know if it works. If not please share the output of the result.

If above solution help you to resolve you query. Please select as the solved.

Best Regards,

Kaustubh

[WassimAB]

thanks a lot Kaustubh... its working

however, am trying to change the format of date to "30Nov2024", any advice?

2024-11-30 
2024-11-30
2024-11-30
2024-11-30
2024-11-30
2024-11-30
2024-11-30

[Kaustubh17]

@WassimAB 

Please use the below formula to get the desired result.

DateTimeFormat(DateTimeParse([YourDateField], "%d%b%Y"), "%Y-%m-%d")

Please let me know if this fixed your requirement.

Best,

Kaustubh