Convert a date from a TEXT formula to quarter

Burubear

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.

 

 

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

Hi

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

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