Taking 1600-2400 and Converting to Time in different cells

  • Thread starter Thread starter gkarasiewicz
  • Start date Start date
G

gkarasiewicz

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

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
 
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
 
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+(B1>C1))*24
 
Back
Top