Convert a date from a TEXT formula to quarter

Hi there,

I would like to create two new items for calculating “Renewal Date” and “Renewal Qtr”. I am able to figure out the calculation of “Renewal Date” but not the “Renewal Date” into quarter.

Sales Type (Format: TEXT)
Date 1(Format: TEXT)
Date 2(Format: TEXT)

The formula of “Renewal Date” is:
IF Sales Type = "Electronic" THEN Date 1 ELSE Date 2 (Format: TEXT)

I can’t use Period (Renewal Date) as it gives me an error that “Renewal Date” is TEXT but not a DATE. How can I convert it into quarter? Thanks.

Accepted answers

Burubear

I have already have the calculated date1 or date2 under the "Renewal Date" as I showed earlier. It goes back to my original question is how can I convert the TEXT into DATE.

If the renewal date is 12/31/2018, what is the right formula to convert this TEXT into Date when I create a new item?

Thanks.

All comments

DavidSmith

It would be much simpler if you format date1 and date2 as dates rather than text

Then you can use period(renewal date) for a line formatted as Time Period:Month and also for Quarter, for a line item formatted as Time Period Quarter

I would also create a list of the Sales Types, so that the user should choose "electronic" rather than free text. it will prevent spelling mistakes.

I would also set up a module dimensioned by this Sales Type list with two line items, Date1? and Date2?, formatted as booleans

Then check which Sales Types needs Date1 and Date2, then the formula would look something like this:

IF Sales Type Details.Date1? THEN PERIOD(Date1) ELSE PERIOD(Date2)

If you needed the Renewal date to be Quarter, enable Quarter totals in the time settings and format Renewal Date as Time Period: Quarter.

Hope this helps

David

Burubear

Hi David,

date1 and date2 as text because they are imported. If I just change the format to DATE, I'll lose the data. What is the best way to convert them into DATE?

I tried to follow your suggestion by creating the Sales Type List as well as the Sales Type Details module. Is this formula for the "Renewal Date"? Are the Date1 and Date2 from my module which stores these two dates? However, it still gives me an error as Date1 and Date2 are TEXT.

IF Sales Type Details.Date1? THEN PERIOD(Date1) ELSE PERIOD(Date2)

Please further explain. Thanks.

DavidSmith

Converting text to a date is not actually that easy

There is a DATE function but that needs three parameters year, month and day, so depending on your date format you would have to use some LEFT, MID and RIGHT functions to create the numbers for Year, Month and Day, and then pull then together into a line item formatted as date.

So I would have the initial formula formatted as text and don't convert it to a date yet

When you have calculated date1 or date2, then convert that into a date in a new line item and then use the period function to return the month or quarter

Hope that helps

David

Burubear

I have already have the calculated date1 or date2 under the "Renewal Date" as I showed earlier. It goes back to my original question is how can I convert the TEXT into DATE.

If the renewal date is 12/31/2018, what is the right formula to convert this TEXT into Date when I create a new item?

Thanks.

DavidSmith

OK, I'll break this up into peices so it's easier to understand

Renewal Year = VALUE(RIGHT(Renewal date,4))

Renewal Month = VALUE(LEFT(Renewal date,2))

Renewal Day = VALUE(MID(Renewal date,4,2))

Renewal Date as Date = DATE(Renewal Year, Renewal Month, Renewal Day)

Hope that sorts it for you

David

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…