Copy Value from another sheet using a function. Not Paste Value.

[mbryson]

Have the text below in a Raw data paste in one sheet. On a second tab where I perform calculations and formatting, I would like to pull out the Starting At Date and the Ending At Date. I know on that sheet I could perform a text to columns with a delimiter of : or even a space. But I write over this raw data every day. I need the text to columns to occur on another sheet that reads from the first. So, lets say the cell below is Sheet1 A3, On Sheet2 B3 I want to pull out the 2nd 06/23/2014 and Sheet2 B5 pull out the 1st 06/23/2014. This will allow me to show the date range covered in the formatted finish report that is distributed.

Filter Interval[Ending At]: 06/23/2014 23:59:59; Interval[Starting At]: 06/23/2014 00:00:00; Interval Length: Entire report range


Any assistance would be greatly appreciated.

Michael

[Jonmo1]

If the date format is consistent, always 2digitmonth/2digitday/4digityear
And they always follow the [Ending At]: and [Starting At]: strings..

You can do

ending date : =MID(Sheet1!A3,FIND("Ending At",Sheet1!A3)+12,10)
starting date : =MID(Sheet1!A3,FIND("Starting At",Sheet1!A3)+14,10)

These return as TEXT strings, not real dates.
You won't be able to use them in any > < comparison functions or lookup functions.

To convert them to real dates, just add +0 to the end of them and format the formula cell as a date.

Hope that helps.

[mbryson]

This worked perfectly. Never used FIND before. Thank you very much.

[Jonmo1]

You're welcome..


Just FYI, it's case sensitive...
It will find "Ending At", but not "ending at" or "ENDING AT" etc..
You can change Find to Search to make it NOT case sensitive.