Copying Date/Time from CRM from 2 fields then calculate minutes in between

[uknowwhoibe]

I have been tasked with making a report (that our CRM is unable to do on its own...different topic) that basically does the following:

I copy/paste record creation date that comes up in this format:

Please Login or Register  to view this content.

Then I copy and paste activity on the record that is in these two formats:

First attempt:

Please Login or Register  to view this content.

Second attempt:

Please Login or Register  to view this content.

From there I need to be able to show the following (without having to manually add up each of these:

- Time in minutes between creation, first attempt and second attempt.
- Ideally I could setup "off-hours" that would not count against this (we close at 6pm until 9am so those aren't counted against; we're closed on Sunday; etc.)

I've tried all kinds of things but I simply cannot get this to convert into dates and it's taking so much time just to manually do this.

Any thoughts or at least get me started? I have until Monday AM to have this completed for roughly 1500 entries and manually doing this takes about 1 minute per.

Thank you in advance!

[JBeaucaire]

The values you are pasting are probably coming into Excel as text. They did when I pasted them into mine.

Data Range

	A	B	C	D	E	F
1	Create	Thu Jun 29, 2017 9:06am	42915.37917			C1: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(B1,5,LEN(B1)),"am"," am"),"pm"," pm"),"AM", " AM"), "PM", " PM")+0
2	1st Att	MON JUL 10, 2017 1:12PM	42926.55	16086	min	D2: =(E2-$E$1)*1440
3	2nd Att	MON JUL 10, 2017 1:15PM	42926.55208	16089	min	D3: =(E3-$E$1)*1440

Column C is converting the text to Excel date/time numerics, column D is doing the math.

[uknowwhoibe]

Thank you! This is very helpful. Is there an easy way to omit off-hours and Sundays in this?