Convert time and round down to nearest 15 minute interval

Hi,

I have a spreadsheet (excel 2010) with a time field pasted in from another sheet that is populated from an external database. Unfortunately, I have no control over the format in which this time field is sent to me. I am trying to convert it to 24 hour time and use either FLOOR or MOD to round to 15 minutes but I cannot seem to convert the time correctly. I have tried using Text-to-columns and then reformatting it still doesn't work. It's almost like it sees it as text. I think the original file may split the time from combined date/time field which may have caused the format issue.

Can anyone suggest how I might convert this? I will be pasting this data into a template daily so would like it as automated as possible.

Thanks in advance

hi, try
=FLOOR(TIMEVALUE(A1),"00:15")

Perfect! Thank you!