Sorry about that. I didn't use the VB Editor to write that code, so it wasn't checking my work for me.I had to amend your posted code (ever so slightly) by removing the If's from the or's and adding a missing ")"
Just FYI:
Now = Date + Time
Now Number Type = Double. Ex: 42710.403484375
Date Number Type ~ Long. Ex: 42710
Time Number Type ~ Decimal. Ex: 0.403484375
Excel Stores all Dates and Times as Doubles.
Now = 42710.403484375 = 12/06/2016 09:42
Date = 42710 = 12/06/2016
Time = 0.403484375 = 09:42
To test, enter some dates and times in column A. Format Cells in Column B as Number with 15 digits. Column B Formulas = "=A1" etc
Format (Time, "hh:mm") = Format (Now, "hh:mm")
Format (Date, "mm, dd, yyyy") = Format (Now, "mm, dd, yyyy")
Format Time or Date (..., "mm, dd, yyyy hh:mm") = Error