Subtracting TIme in Excel

[Starrizzo]

I have a SS that has the schedules of employees.

for example A1 = 4pm-8pm, B1 = 4pm-10pm

How would find the total hours worked for the A1 and B1

 

[longhair]

Starrizzo,
rather then have the time in 1 cell how sbout splitting into 2 rows.
for example:
a1 = 16:00
a2 = 20:00
then subtract a1 from a2 in a3.
regards,
longhair

 

[SkipVought]

FYI...

faq68-5827

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[yogia]

Hi starrizzo:

Try my following convoluted formula in cell A2 ...

=SUBSTITUTE(MID(A1,FIND("-",A1)+1,255),"PM"," PM")-SUBSTITUTE(LEFT(A1,FIND("-",A1)-1),"PM"," PM")

then copy it to B2

and the resulting value in cells A2 and B2 will be
4:00 and 6:00 respectively.





Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

I came in on this one kind of late, after Starrizzo had already been well advised that times should be stored as times, not as text strings.

But (largely to amuse myself) I went ahead and came up with the following formulas that will separate the input into actual times in two different fields. This might actually be useful to Starrizzo if (s)he has a lot of schedules that someone already stored in the "4pm-10pm" format and (s)he now needs to break those times apart.

Say you have "4pm-10pm" in A2:

In B2, type
[tab][COLOR=blue white]=TIMEVALUE(LEFT(A2, FIND("-", A2) - 3) & " " & MID(A2, FIND("-", A2) - 2, 2))[/color]
to get the start time

In C2, type:
[tab][COLOR=blue white]=TIMEVALUE(MID(A2, FIND("-", A2) + 1, LEN(A2) - FIND("-", A2) - 2) & " " & RIGHT(A2, 2))[/color]
to get the end time.

Then in D2 a simple [COLOR=blue white]=C2 - B2[/color] will give you the total scheduled hours.


Yogi - your formula doesn't work for me. I believe it is because the input given in the OP has "pm" in lower case ("4pm-8pm" or "4pm-10pm") and SUBSTITUTE is case-sensitive. it works fine for "4PM-8PM". It would also have trouble with times in the AM.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]



Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Yogi - your formula doesn't work for me. I believe it is because the input given in the OP has "pm" in lower case ("4pm-8pm" or "4pm-10pm") and SUBSTITUTE is case-sensitive. it works fine for "4PM-8PM". It would also have trouble with times in the AM.

Thanks John!

The case of upper case and lower case pm, AM, etc., can be fixed by using ...

=SUBSTITUTE(MID(UPPER(A1),FIND("-",A1)+1,255),"PM"," PM")-SUBSTITUTE(LEFT(UPPER(A1),FIND("-",A1)-1),"PM"," PM")

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Seems like the OP is AWOL.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]