How to convert a text string to a date
I know this question has been asked before, and I've tried to implement the solutions in the other threads, but I cannot get it to work in my situation
This is what my sheet looks like:
I have separated the [Billing Period] string exported by my system using RIGHT and LEFT for the Start and End dates respectively. At this point, they're still text.
In the last column, I want to calculate how many day have elapsed since the end of the period using =TODAY()-[Billing Period End Date]@row. I'm not opposed to a helper column inbetween
I have tried the following, without success:
1) Convert the billing period end date column to a date column. I get an #INVALID OPERATION with the subtraction formula
2) I have tried converting the extracted date string (still in a text column) using:
=DATE(VALUE(LEFT([Billing Period End Date]@row, 2), VALUE(MID([Billing Period End Date]@row, 4, 2)), VALUE(RIGHT([Billing Period End Date]@row, 4))))
This gives me #INCORRECT ARGUMENT SET
Can anyone offer any direction on how I can accomplish this date calculation?
Thank you in advance.
Best Answer
-
Hello @AFlint
I would suggest converting your Start and End Dates into real Dates, and then use those values to do the math.
The DATE function has the syntax (YYYY, MM, DD)- it appears the formula above was using MM,DD, YYYY format.
Parse your original Billing Date range data into the End Date and Beginning Date columns, using the VALUE function as you do this, arranging the parsed elements to the correct syntax configuration to use the DATE function. This will convert the parsed data directly into Date formats. Both the End Date and Beginning Date columns need to be formatted as Date columns.
For example, your Start Date formula would look something like this (this is now a DATE formatted column):
=DATE(VALUE(MID([Billing Period]@row, 7, 4)), VALUE(LEFT([Billing Period]@row, 2)), VALUE(MID([Billing Period]@row, 4, 2)))
Your math should work as you wrote in your first formula. Please note there are also formulas specifically for this calculation.
Does this work for you?
Answers
-
Hello @AFlint
I would suggest converting your Start and End Dates into real Dates, and then use those values to do the math.
The DATE function has the syntax (YYYY, MM, DD)- it appears the formula above was using MM,DD, YYYY format.
Parse your original Billing Date range data into the End Date and Beginning Date columns, using the VALUE function as you do this, arranging the parsed elements to the correct syntax configuration to use the DATE function. This will convert the parsed data directly into Date formats. Both the End Date and Beginning Date columns need to be formatted as Date columns.
For example, your Start Date formula would look something like this (this is now a DATE formatted column):
=DATE(VALUE(MID([Billing Period]@row, 7, 4)), VALUE(LEFT([Billing Period]@row, 2)), VALUE(MID([Billing Period]@row, 4, 2)))
Your math should work as you wrote in your first formula. Please note there are also formulas specifically for this calculation.
Does this work for you?
-
Thank you, @Kelly Moore
The formula worked perfectly. The only modification I needed for the End Date was to change the LEFT to a second MID and add a starting character to get the month.
The other date functions you referenced could be very helpful, but I need actual calendar days elapsed between the end of the billing period and the day the report is being updated. That's the reason I was going to subtract from TODAY(). Is there another function I should use?
-
Your subtraction formula works- the functions listed are offered as additional options. The different functions potentially give different results, depending if the dates involve weekends or not- you would use NETDAYS to be equivalent to your formula. TODAY() can be used as either a start date or end date within any of those listed functions.
If you choose to use NETDAYS, the formula will be
=NETDAYS([Billing Period End Date]@row, TODAY())
Your formula works and that's really all that matters. I'm glad you got it sorted
Kelly
Help Article Resources
Categories
Check out the Formula Handbook template!
