Week start date from quickbooks export text range date

[B5rocksass]

I have a date question about Quickbook exports into excel. I'm compiling information from that report into a different tab but I'm having issues with the dates. They export as a week range and appear in a text format, for example Jan 22-28, 2017. I'm trying to find a formula that will recognize this as a date and show me the week start date. I've used Transpose, then text to columns to separate by the hyphen and change to MDY format, and then transpose again. There has to be an easier way.

 

[rstewart5910]

Something that looks like this?

TEXT	DATE
Jan 22-28, 2017	1/22/17

<tbody>
</tbody>

As long as the year is inconsequential or can be assumed to be the current year, you could just use:

=--LEFT(A2,FIND("-",A2)-1)

Then ensure the format of the cell is a data format.

If you need the year, because you are crossing years, then use:

=--(LEFT(A2,FIND("-",A2)-1)&", "&RIGHT(A5,4))

Probably an easier way to write this, but this works. Double negative turns the result into a number. Make sure you format the cell as a "date".

 

[B5rocksass]

These both work great...its just that I need it to see last year data as well. So if I have 2016 data and 2017 data, how do I get a formula to recognize which year it is without also having to write in the year above those dates. Or if I have to write in the year above those dates, is there another formula that will extract the year from the text string that I've given? Perhaps a combined formula that does both?

 

[rstewart5910]

Use the 2nd formula. It should recognize the year.