DATE         | TIME    | DATETIME
-----------------------------------
2014-08-01   | 23:45   |

=TEXT(G2,"yyyy-mm-dd")&" " & TEXT(H2, [what should I put here?])

=TEXT(G2,"yyyy-mm-dd")&" "&TEXT(H2,"hh:mm:ss.000")
                                         ^^^^^^^

=TEXT(G2,"yyyy-mm-dd")&" "&TEXT(H2,"hh:mm")&":00.000"

=(TEXT(TODAY(),"m/dd/yy")&" "&TEXT("17:00","hh:mm")) -> convert text to date format