How to convert a text string to a date

AFlint

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.

Kelly Moore

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.

https://help.smartsheet.com/function/netdays

https://help.smartsheet.com/function/networkday

https://help.smartsheet.com/function/networkdays

Does this work for you?

Kelly Moore

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.

https://help.smartsheet.com/function/netdays

https://help.smartsheet.com/function/networkday

https://help.smartsheet.com/function/networkdays

Does this work for you?

AFlint

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?

Kelly Moore

@AFlint

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