Date time stamp issue

[KELLIS]

Hi Guys

I have a spreadsheet where the data is copied and pasted from an email by my colleagues One columns data has date and time shown as 01-APR-12 16:51 is there a way I can remove the date portion without adding a separate column, all my colleagues should have to do is copy and paste.

I also have another column called 'Standby' where I want to use formula to show 'Yes' or 'No' if the time portion is after 16:00hrs and before 07:00hrs hence the reason I need to remove the date portion.

Please can someone help? example spreadsheet available if required.

Thanks

[ChemistB]

Is the date and time formatted as a number? To check this, let's say the date/time is in G5, in a blank cell, type = ISNUMBER(G5) and see if you get a true or false.

If the date and time is a number, you can modify your formula so that the date is removed at that point.

=IF(OR(MOD(G5,1)<= "7:00"+0, MOD(G5,1)>= "16:00"+0),"Yes", "No")