A quickie for you

  • Thread starter Thread starter Abhijat
  • Start date Start date
A

Abhijat

Hi Champs!
I got stuck in a string operation in excel. I hope you can help.
In a cell we have 16:15, 17:15 & 19:01 as ="16:15" & ", " & "17:15" &
" & " & "19:01". We want to add four and haf hours to each and display
in the adjacent cell.
The best I could come up with was: =TIME(LEFT(LEFT(E5,5),2),RIGHT(LEFT
(E5,5),2),0)+TIME(4,30,0) & ", " & TIME(LEFT(RIGHT(LEFT(E5,12),5),
2),RIGHT(RIGHT(LEFT(E5,5),5),2),0)+TIME(4,30,0) & " & " & TIME(LEFT
(RIGHT(E5,5),2),RIGHT(RIGHT(E5,5),2),0)+TIME(4,30,0), where E5 is the
cell where initial times are.

which resulted in 0.864583333333333, 0.90625 & 0.979861111111111 :).
Thats when I stopped and turned to this group.

Thanks for your help.!

Best Regards,
Abhijat
 
Hi Champs!
I got a quick fix myself ....
its
=LEFT(LEFT(E5,5),2)+4 &":"& RIGHT(LEFT(E5,5),2)+30 & ", " & LEFT(RIGHT
(LEFT(E5,12),5),2) + 4 & ":" & RIGHT(RIGHT(LEFT(E5,5),5),2)+30 & " & "
& LEFT(RIGHT(E5,5),2)+4 & ":" & RIGHT(RIGHT(E5,5),2)+30

it gives the result as
20:45, 21:45 & 23:31


Thanks a lot

Best Regards,
Abhijat
 
Back
Top