Taking 1600-2400 and Converting to Time in different cells
 

Okay here is my issue.

I have the 1600-2400 in A1

now in B1 I want 1600(Start Time) to be a time value

And in C1 i want 2400(End Time) to be a time value

Then in D1 i want to find out the total hours, in this case 8

Taking 1600-2400 and Converting to Time in different cells
 

Hi

Excel stores times as fractions of a day (24 hours) so use
=(C1-B1)*24

--

Regards
Roger Govier

"gkarasiewicz" wrote in message
...
Okay here is my issue.

I have the 1600-2400 in A1

now in B1 I want 1600(Start Time) to be a time value

And in C1 i want 2400(End Time) to be a time value

Then in D1 i want to find out the total hours, in this case 8

Taking 1600-2400 and Converting to Time in different cells
 

=--(LEFT(A1,2)&":"&MID(A1,3,2))

=--(MID(A1,6,2)&":"&RIGHT(A1,2))

and

=(C1-B10*24

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"gkarasiewicz" wrote in message
...
Okay here is my issue.

I have the 1600-2400 in A1

now in B1 I want 1600(Start Time) to be a time value

And in C1 i want 2400(End Time) to be a time value

Then in D1 i want to find out the total hours, in this case 8

Taking 1600-2400 and Converting to Time in different cells
 

gkarasiewicz

A1 16:00
B1 24:00
C1 =(B1-A1)*24
C1=8.00 (formate as Number)

Because excel stores time as a parts of a day you need to multiply by 24 to
return your answer in hours, otherwise you will have .3333 of a day.

Mike Rogers

"gkarasiewicz" wrote:

Okay here is my issue.

I have the 1600-2400 in A1

now in B1 I want 1600(Start Time) to be a time value

And in C1 i want 2400(End Time) to be a time value

Then in D1 i want to find out the total hours, in this case 8

Taking 1600-2400 and Converting to Time in different cells
 

Assuming times before 1200 are shown with a leading 0...

A1: '1600-2400
B1: =TIME(LEFT(A1,2),MID(A1,3,2),0)
C1: =TIME(MID(A1,6,2),RIGHT(A1,2),0)
D1: =24*MOD(C1-B1,1)

Format D1 as General

Rick


"gkarasiewicz" wrote in message ...
Okay here is my issue.

I have the 1600-2400 in A1

now in B1 I want 1600(Start Time) to be a time value

And in C1 i want 2400(End Time) to be a time value

Then in D1 i want to find out the total hours, in this case 8

Taking 1600-2400 and Converting to Time in different cells
 

Try this formula in B1

=TEXT(LEFT(A1,4),"00\:00")+0

and in C1

=TEXT(RIGHT(A1,4),"00\:00")+0

format both of these cells as hh:mm and then for total hours in D1

=(C1-B1+(B1C1))*24

"Rick Rothstein (MVP - VB)" wrote:

Assuming times before 1200 are shown with a leading 0...

A1: '1600-2400
B1: =TIME(LEFT(A1,2),MID(A1,3,2),0)
C1: =TIME(MID(A1,6,2),RIGHT(A1,2),0)
D1: =24*MOD(C1-B1,1)

Format D1 as General

Rick


"gkarasiewicz" wrote in message ...
Okay here is my issue.

I have the 1600-2400 in A1

now in B1 I want 1600(Start Time) to be a time value

And in C1 i want 2400(End Time) to be a time value

Then in D1 i want to find out the total hours, in this case 8