difference between times

daniels012

Well-known Member

Joined

Jan 13, 2005

Messages

5,219

• Aug 15, 2007

• #2

11:12PM 2:00AM

Click to expand...

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

Click to expand...

Then Ok

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


michael

 

Upvote 0

Datsmart

Well-known Member

Joined

Jun 19, 2003

Messages

7,985

• Aug 15, 2007

• #3

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

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Aug 15, 2007

• #4

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

Code:

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

D2

Code:

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

E2

Code:

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

 

Upvote 0

daniels012

Well-known Member

Joined

Jan 13, 2005

Messages

5,219

• Aug 15, 2007

• #5

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

 

Upvote 0

barry houdini

MrExcel MVP

Joined

Mar 23, 2005

Messages

20,825

• Aug 15, 2007

• #6

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

 

Upvote 0

Datsmart

Well-known Member

Joined

Jun 19, 2003

Messages

7,985

• Aug 15, 2007

• #7

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,

 

Upvote 0

daniels012

Well-known Member

Joined

Jan 13, 2005

Messages

5,219

• Aug 15, 2007

• #8

Very nice Barry!

You can also change:

Code:

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

to

Code:

=(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

 

Upvote 0