Calculate Time Difference Between Time In HH:MM

**Stalker** · 03-25-2013, 01:38 PM

Morning all,

Range A4:A9 contains times (14:00-15:00, 15:00-16:00, 16:00-17:00, 17:00-18:00, 18:00-18:30, 19:00-20:00)

in range B4:B9 i have - (=C4, =C5, =C6, =C7, =C8/2, =C9)

the cell with C8/2 is due to the fact that in Cell A8 the time is only half an hour, the rest are a full hour.

I want to make the formulas in B4:B9 more felxible, as i modify the sheet to fit its purpose, the times in A4:A9 may not always be the same (but will alwyas be either a full hour or a half hour)

The intention is to have a formula in B4:B9 that will see if the times difference is a full hour or a half, if its a full hour then just show me the figure from C, if it is a half hour, show me half of the figure in C.
I thought i could do it by -

Code:

=IF(LEFT(A4,2)-RIGHT(A4,2)=1,C4,C4/2)

but that just gives me a figure of 1.
Which thinking about it was doomed to fail as the RIGHT(A4,2) will only show 2 zeros lol

Any suggestions please?

KR

**Excel Fox** · 03-25-2013, 11:56 PM

Not the most elegant one, but did you try

=IF(ROUND((TIMEVALUE(RIGHT(A5,5))-TIMEVALUE(LEFT(A5,5)))*24,10)=1,C5,C5/2)

**Stalker** · 03-26-2013, 12:44 PM

**Rick Rothstein** · 03-26-2013, 10:47 PM

Originally Posted by Stalker

Is it elegant? no
Do i care? no
Does it work? yes - Thankyou very much indeed!

Some may consider this "elegant" (I'm not sure what that word actually means as it applies to formulas

), but if not, it is definitely shorter

...

=24*C4*(RIGHT(A4,5)-LEFT(A4,5))

**Excel Fox** · 03-27-2013, 08:47 AM

Elegant - antonyms: Clunky, Inefficient, Not-the-best-approach (Ref. Excel Fox)

**Rick Rothstein** · 03-27-2013, 09:42 AM

Originally Posted by Excel Fox

Inefficient, Not-the-best-approach (Ref. Excel Fox)

May I ask in what way?

**Excel Fox** · 03-27-2013, 11:29 AM

Rick, I was referring to what I meant by being 'elegant'. For example, mine was clunky, you proved it. Mine was inefficient, you proved it, mine was not the best approach, you proved it.

**Rick Rothstein** · 03-27-2013, 11:46 AM

Originally Posted by Excel Fox

Rick, I was referring to what I meant by being 'elegant'. For example, mine was clunky, you proved it. Mine was inefficient, you proved it, mine was not the best approach, you proved it.

Oh, I thought you were directing those comments at my formula and I was wondering in what way it had fallen short. I thought maybe you were saying there was some kind of hidden timing issue with my code (which your use of TIMEVALUE avoided) that I was not aware of, so I was asking you to clarify that for me.