vba excel: combine 2 cell values to 1 cell

mike637 used Ask the Experts™
Hello Experts,

I am trying to combine 2 cells into one, but have a few "if" issues.
I am attaching a workbook example to help.

I want to take the cell value of "B1" which is 6:00 and combine it with cell value of "C1" which is 4:00 and have that show in "B14" as 6 - 4.  
In this scenerio I am taking the first numeric figure from each cell, separated with a " - " character.

I will then move on to D1 & E1 etc.

The "if"
Is when the time is anything other than the whole hour.  For example if "B1" = 6:30 then it would truncate the 0, leaving the 6:3 add the " - " then the first digit in cell "C1".
Range "B14" would show 6:3 - 4 (if this is impossible - then the whole 6:30 would have to be worked with, if it not a whole hour)

Is this possible?  I have tried a few things - but this is out of my league.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

check this
Sr. System Analyst
here it is:

=MOD(HOUR(F1),12) & IF(MINUTE(F1)=0, "", ":" & (MINUTE(F1)/10)) & "-" & MOD(HOUR(G1),12) & IF(MINUTE(G1)=0, "", ":" & (MINUTE(G1)/10))

adjust the cells for others


Great - Thank you very much!!
Another formula

=SUBSTITUTE(TEXT(SUBSTITUTE(TEXT(A1,"h:mm"),":","."),"General"),".",":")&" - "&SUBSTITUTE(TEXT(SUBSTITUTE(TEXT(B1,"h:mm"),":","."),"General"),".",":")

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial