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)))
Answers
-
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
0 -
Thank you!!0
-
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
1 -
@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!
0 -
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
0 -
The data will roll up, the view i sent was of the raw data in the data hub. Not aggregated yet.
0 -
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
0
