I had to amend your posted code (ever so slightly) by removing the If's from the or's and adding a missing ")"
Sorry about that. I didn't use the VB Editor to write that code, so it wasn't checking my work for me.



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