Date Conversion

I need to convert the text formatted data below to the date format 03/08/2022. I have a formula to do that, but the time stamp on the right interferes with my formula. How would I remove that part of the cell? Or how would I add it to my formula: DATE(VALUE(RIGHT(As Of Date, 4)), VALUE(CODE(FINDITEM(Time Conversion, MID(As Of Date, 4, 3)))), VALUE(LEFT(As Of Date, 2)))

Accepted answers

All comments

TimothyThomas

Hi @melliott ,

If it's feasible, I think it would make most sense to create an intermediate line item where you are pulling the time stamp off of the underlying text field, and then use that intermediate line item in your calculations. It would simply be LEFT(As of Date,11) - this assumes there will always be 11 characters for the date itself, but that can be made variable if necessary.

It may in fact make sense to break up each portion of the formula (the day, month, and year) separately and then concatenate them all together, but is not necessary.

If you do not want any additional line items, wherever you are currently referencing As of Date you can replace with the LEFT(As of Date,11) formula. For example, for the year portion of the formula, it would then be DATE(VALUE(RIGHT(LEFT(As of Date,11),4))) - though as you can see, at this point it's getting rather lengthy, which is why separate line items would probably be best.

Hope that helps,

Timmy

melliott

Thank you!!

rob_marshall

@melliott

Let me play Devil's adovcate...why are all those records the same date? Are they actually part of the transaction or can you put that field in a SYS module and only load it once?

Rob

melliott

@rob_marshall All different transactions that ran the same day, huge list. I found a work around and fixed the date, works how I need it to. Thanks for the input!

rob_marshall

@melliott

Are the users planning by the day? Just trying to figure out why you have transactional level detail, why not have the data aggregated up to the granularity that they plan at, whether that is weekly or monthly.

Rob

melliott

The data will roll up, the view i sent was of the raw data in the data hub. Not aggregated yet.

rob_marshall

@melliott

That is kind of my point, why not aggregate it at the source and only bring in the aggregated data to the hub? Does the detailed data serve a purpose?

Rob

Quick Links

Can you help?

Unanswered questions

Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…