difference between times

[dashing_peep08]

11:12PM 2:00AM
8:46AM 9:32AM
9:32AM 10:05AM
10:05AM 10:17AM
10:17AM 10:38AM
10:38AM 10:51AM
10:51AM 11:00AM
11:00AM 11:17AM
11:17AM 12:11PM
12:11PM 12:37PM
12:38PM 1:06PM
1:06PM 1:17PM

supposed i have these log in and log out. how do i get each of the time elapsed. pls note that there is no space between time and the text am or pm. and some of the times are from am to pm. its easy if they are both pm or am but when they are changing from noon to am or am to noon im having this problem

Please help me.

thanks

 

[daniels012]

11:12PM 2:00AM

Please put your exact text. It looks like there is a space between the first AM and a number.

If you mean there is no space between the number and PM
like:

11:12PM

Then Ok

Is your data formatted as text, general, number, time????


michael

 

[Datsmart]

Excel Workbook
	A	B	C	D	E
1	Log In	Log Out	Log In	Log Out	Elapsed
2	11:12PM	2:00AM	11:12 PM	2:00 AM	2:48
3	8:46AM	9:32AM	8:46 AM	9:32 AM	0:46
4	9:32AM	10:05AM	9:32 AM	10:05 AM	0:33
5	10:05AM	10:17AM	10:05 AM	10:17 AM	0:12
6	10:17AM	10:38AM	10:17 AM	10:38 AM	0:21
7	10:38AM	10:51AM	10:38 AM	10:51 AM	0:13
8	10:51AM	11:00AM	10:51 AM	11:00 AM	0:09
9	11:00AM	11:17AM	11:00 AM	11:17 AM	0:17
10	11:17AM	12:11PM	11:17 AM	12:11 AM	12:54
11	12:11PM	12:37PM	12:11 AM	12:37 AM	0:26
12	12:38PM	1:06PM	12:38 AM	1:06 PM	12:28
13	1:06PM	1:17PM	1:06 PM	1:17 PM	0:11
Sheet

 

[Jonmo1]

personal.xls
	A	B	C	D	E
1	Start	End	AdjStart	AdjEnd	Elapsed
2	11:12PM	2:00AM	11:12:00PM	2:00:00AM	2:48:00
3	8:46AM	9:32AM	8:46:00AM	9:32:00AM	0:46:00
4	9:32AM	10:05AM	9:32:00AM	10:05:00AM	0:33:00
5	10:05AM	10:17AM	10:05:00AM	10:17:00AM	0:12:00
6	10:17AM	10:38AM	10:17:00AM	10:38:00AM	0:21:00
7	10:38AM	10:51AM	10:38:00AM	10:51:00AM	0:13:00
8	10:51AM	11:00AM	10:51:00AM	11:00:00AM	0:09:00
9	11:00AM	11:17AM	11:00:00AM	11:17:00AM	0:17:00
10	11:17AM	12:11PM	11:17:00AM	12:11:00PM	0:54:00
11	12:11PM	12:37PM	12:11:00PM	12:37:00PM	0:26:00
12	12:38PM	1:06PM	12:38:00PM	1:06:00PM	0:28:00
13	1:06PM	1:17PM	1:06:00PM	1:17:00PM	0:11:00
Sheet1

Formula in C2 filled down

=--SUBSTITUTE(SUBSTITUTE(A2,"AM"," AM"),"PM"," PM")

D2

=--SUBSTITUTE(SUBSTITUTE(B2,"AM"," AM"),"PM"," PM")

E2

=IF(D2<C2,(D2+1)-C2,D2-C2)

 

[daniels012]

Datsmart,
Is this Excel Jeanie HTML 4 a paid for thing??
I like it, I am just not sure I want to pay for it.

Michael

 

[barry houdini]

Assuming log in "time" in A2 and log out in B2 then with a single formula

=MOD(REPLACE(B2,LEN(B2)-1,0," ")-REPLACE(A2,LEN(A2)-1,0," "),1)

format as time

 

[Datsmart]

Daniels,
Excel Jeanie is free if you don't mind the "nag" pop up every now and then. Some features are not available until you register, but it works fine as downloaded for trial. Don't think the trial runs out...

Barry,
Another one or your masterful formula!
I will archive that one.

Thanks,

 

[daniels012]

Very nice Barry!

You can also change:

=MOD(REPLACE(B2,LEN(B2)-1,0," ")-REPLACE(A2,LEN(A2)-1,0," "),1)

to

=(MOD(REPLACE(B2,LEN(B2)-1,0," ")-REPLACE(A2,LEN(A2)-1,0," "),1))*24

without changing the format to give you a whole number representation.

Michael