Calculate Time Difference Between Time In HH:MM

[Stalker]

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

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]

:D

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


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg (https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg)
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eekDyfS0 CD (https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eekDyfS0 CD)
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eevG7txd 2c (https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eevG7txd 2c)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg)
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg (https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg)
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg (https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg)
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg.9dPo-OdLmZ09dc21kigjmr (https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg.9dPo-OdLmZ09dc21kigjmr)
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg (https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg)
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg.9cXui6zzkz09cZttH_-2Gf (https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg.9cXui6zzkz09cZttH_-2Gf)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

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 :confused:), but if not, it is definitely shorter :peacesign:...

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

[Excel Fox]

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

[Rick Rothstein]

Inefficient, Not-the-best-approach (Ref. Excel Fox)
May I ask in what way?

[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.

[Rick Rothstein]

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.

[Stalker]

Well, they both work, so thanks very much for the input!