Extract Date/Time to Date and Time

Sorry - This dosn't work I'll have another try!
Daddylonglegs is absolutely correct
You can't simply use Format Cells

Range "A1" format cells Custom dd/mm/yyyy hh:mm AM/PM
Range "A2" format cells Date *14/03/2001
Range "A2" =A1
Range "A3" format cells Time 1:30:55 PM
Range "A3" =A1
Range "A4" format cells General
Range "A4" = IF(AND(A3>12,A3<5),"Night Shift","Day Shift")

Might be easier to use Columns rather than Rows in your sheet.
ie A2 becomes B1, A3 becomes C1, A4 becomes D1
That way you can format each column, then use Fill Down for B1 to D1
as required.

Only a thought.