Working With Different Time/Date Formats and Formulas

[Gator1130]

I am trying to find out the number of minutes that have gone by between two different times. The data given to me is presented like this:
[FONT=&quot]
1st Date and Time (2 cells)

8/11/2018 06:48:36pm

2nd Date and Time (1 Cell)

08/11/18 06:43p

The dates are pretty much irrelevant as they are almost always going to be the same day. How can I format these cells so that I can then find the amount of time elapsed between the two in minutes?[/FONT]

 

[JoeMo]

Assuming your dates/times are all in the same row, the formulas below can be used to get the elapsed time in minutes between them. The columns with headers Text1 and Text2 are your raw data which have to be converted to dates and times. Copy the formulas down to cover all your data.

Excel Workbook
	A	B	C	D	E	F	G
1	Date1	Text1	Time1	Text2	Date2	Time2	Elapsed Minutes
2	8/11/2018	06:48:36pm	6:48:36 PM	08/11/18 06:43p	8/11/2018	6:43:00 PM	5.60
3	8/10/2018	8:47am	8:47:00 AM	8/11/18 06:40:05p	8/11/2018	6:40:05 PM	2033.08
Sheet13

 

[Dave Patton]

Excel 2010
	A	B	C	D
2	11-08-18	6:48:36 PM	5.00	TRUE
3		11-08-18 06:43:36 PM	5.00	TRUE
4		11-08-2018 06:43:36p	5.00	FALSE
5		11-08-2018 06:43p	5.60	FALSE
6
6cc

Cell Formulas
Range	Formula
C2	=((A2+B2)-B3)*1440
C3	=(B2-MOD(B3,1))*1440
C4	=(B2-(TIMEVALUE(MID(B4,13,8))+(RIGHT(B4,1)="p")*0.5))*1440
C5	=(B2-(TIMEVALUE(MID(B5,13,5))+(RIGHT(B5,1)="p")*0.5))*1440
D2	=ISNUMBER(B2)

 

[Gator1130]

Assuming your dates/times are all in the same row, the formulas below can be used to get the elapsed time in minutes between them. The columns with headers Text1 and Text2 are your raw data which have to be converted to dates and times. Copy the formulas down to cover all your data.
Sheet13

	A	B	C	D	E	F	G
Date1	Text1	Time1	Text2	Date2	Time2	Elapsed Minutes

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:75px;"><col style="width:84px;"><col style="width:81px;"><col style="width:112px;"><col style="width:75px;"><col style="width:80px;"><col style="width:111px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]8/11/2018[/TD]
[TD="align: right"]06:48:36pm[/TD]
[TD="align: right"]6:48:36 PM[/TD]
[TD="align: right"]08/11/18 06:43p[/TD]
[TD="align: right"]8/11/2018[/TD]
[TD="align: right"]6:43:00 PM[/TD]
[TD="align: right"]5.60[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]8/10/2018[/TD]
[TD="align: right"]8:47am[/TD]
[TD="align: right"]8:47:00 AM[/TD]
[TD="align: right"]8/11/18 06:40:05p[/TD]
[TD="align: right"]8/11/2018[/TD]
[TD="align: right"]6:40:05 PM[/TD]
[TD="align: right"]2033.08[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =IF(ISNUMBER(SEARCH("p",B2)),REPLACE(B2,SEARCH("p",B2),2," PM")+0,REPLACE(B2,SEARCH("a",B2),2," AM")+0) E2 =LEFT(D2,SEARCH(" ",D2)-1)+0 F2 =IF(ISNUMBER(SEARCH("p",D2)),MOD(REPLACE(D2,FIND("p",D2),2," PM")+0,1),MOD(REPLACE(D2,FIND("a",D2),2," AM")+0,1)) G2 =IF(A2=E2,MOD(ABS(F2-C2),1)*24*60,(ABS(A2-E2)+MOD(ABS(F2-C2),1))*24*60) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you! It worked perfectly!

 

[JoeMo]

Thank you! It worked perfectly!

You are welcome - thanks for the reply.