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

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

In Database

Interface Tools

In-Database

Accepted answers

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

WasimSolution.yxmd

All comments

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

WasimSolution.yxmd

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

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

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15866

MarqueeCrew 13708

apathetichell 13703