Hi all
new to this forum and desperate for help, please!
I am trying to extract specific dates from an audit trail for a booking, which is all in one cell. The audit trail looks like this, it is in cell A2:
Created at 02/01/2013 08:16 by Smith, John Updated at 02/01/2013 08:16 by Smith, John Changed Product [Custom field] from "" to "Product A" Changed Booking Location [Custom field] from "" to "On Site" Changed Region of Project [Custom field] from "" to "Europe" Updated at 18/01/2013 11:15 by Doe, Jane Changed Hours Booked from "427.50" to "378.75" Changed Booking type from "Request - Hard Booking" to "Approved - Hard Booking" Changed Start Date from "2013-01-16" to "2013-02-04" Updated at 07/02/2013 11:50 by Doe, Jane Changed Hours Booked from "378.75" to "217.50" Changed End Date from "2013-06-28" to "2013-03-29" Changed Region of Project [Custom field] from "Europe" to "Europe - South" Changed % Booked from "50.00" to "100.00" Changed Start Date from "2013-02-04" to "2013-02-18" Updated at 06/03/2013 14:11 by Doe, Jane Changed Hours Booked from "217.50" to "112.50" Changed End Date from "2013-03-29" to "2013-03-08" Updated at 08/03/2013 05:44 by Doe, Jane Changed Hours Booked from "112.50" to "82.50" Changed End Date from "2013-03-08" to "2013-03-04"
The first step of the trail starts with "Created", all others steps with "Updated".
I want to extract the date highlighted in red, which is the date when a booking type was first updated to "Approved - Hard Booking" or "Approved - Soft Booking" (this can happen multiple times, but I only want the first date of this audit trail step). The length of the string can vary, also the number of characters between the date I want to extract and the "Approved - Hard Booking".
I think this must be possible with left/right/mid formulas which I've done before, but this one exceeds my Excel skills by far. Can anyone help me, please?
Many thanks in advance!
Tom
new to this forum and desperate for help, please!
I am trying to extract specific dates from an audit trail for a booking, which is all in one cell. The audit trail looks like this, it is in cell A2:
Created at 02/01/2013 08:16 by Smith, John Updated at 02/01/2013 08:16 by Smith, John Changed Product [Custom field] from "" to "Product A" Changed Booking Location [Custom field] from "" to "On Site" Changed Region of Project [Custom field] from "" to "Europe" Updated at 18/01/2013 11:15 by Doe, Jane Changed Hours Booked from "427.50" to "378.75" Changed Booking type from "Request - Hard Booking" to "Approved - Hard Booking" Changed Start Date from "2013-01-16" to "2013-02-04" Updated at 07/02/2013 11:50 by Doe, Jane Changed Hours Booked from "378.75" to "217.50" Changed End Date from "2013-06-28" to "2013-03-29" Changed Region of Project [Custom field] from "Europe" to "Europe - South" Changed % Booked from "50.00" to "100.00" Changed Start Date from "2013-02-04" to "2013-02-18" Updated at 06/03/2013 14:11 by Doe, Jane Changed Hours Booked from "217.50" to "112.50" Changed End Date from "2013-03-29" to "2013-03-08" Updated at 08/03/2013 05:44 by Doe, Jane Changed Hours Booked from "112.50" to "82.50" Changed End Date from "2013-03-08" to "2013-03-04"
The first step of the trail starts with "Created", all others steps with "Updated".
I want to extract the date highlighted in red, which is the date when a booking type was first updated to "Approved - Hard Booking" or "Approved - Soft Booking" (this can happen multiple times, but I only want the first date of this audit trail step). The length of the string can vary, also the number of characters between the date I want to extract and the "Approved - Hard Booking".
I think this must be possible with left/right/mid formulas which I've done before, but this one exceeds my Excel skills by far. Can anyone help me, please?
Many thanks in advance!
Tom
Most of the thanks should go to Rick as he supplied the base formula
