I’m stuck…Trying to convert time to a required format...

I have a series of time at which an engine was started in a single column. The data is as follows:

1. 9:11 AM
2. 9:38 AM
3. 10:19 AM
4. 10:22 AM
5. 10:31 AM
6. 10:40 AM
7. 11:06 AM
8. 11:06 AM
9. 11:36 AM
10. 12:11 PM
11. 12:14 PM
12. 12:46 PM

This continues over a 1000+ rows....

What I need to do is Format this column in such a way that:

• 09:11AM should be formatted to 09:00AM i.e. any time (in minutes) that is between 0-14 minutes.
• 09:38AM should be formatted to 09:30AM i.e. any time (in minutes) that is between 15-44 minutes.
• 12:46PM should be formatted to 01:00PM i.e. any time (in minutes) that is between 45-59 minutes.

I’ve tried enough, but don’t seem to get anywhere. Every time I need to convert it into decimal using =HOUR(F38)+MINUTE(F38)/100, and then do some manual work.

Is there a formula, may be using IF that can assist me format this easily.

I’ve attached a spreadsheet with the data as well.

Please advise.

You could put this formula in B2:

=CEILING(IF(ISNUMBER(A2),A2,VALUE(LEFT(A2,LEN(A2)-3)&":00")+IF(RIGHT(A2,2)="PM",0.5,0))-"00:15:00",--"00:30:00")

format as time, and then copy down to give you what you want.

Hope this helps.

Pete

Thanks Pete,

This is "MAGIC" as I see it:-)

But I'm stuck specially when the time is 12:17 PM, 12:36 PM, 12:58 PM and so on and so forth. The formula works well for the others, but as soon as the time touches the 12th hour mark it returns correct hour but not the corresponding meridiem i.e PM. In these cases, the value returned is 12:00 AM, 12:30 AM and 1:00 AM instead of 12:00 PM, 12:30 PM and 1:00 PM.

I've attached the data after populating the formula. Request you to please see the cells highlighted in "Green" and advise....

Once again thanks for all the great help!

Best,
Jai

Sorry about that. Here's an amended formula to put in B2 which gives you the correct results:

=CEILING(IF(ISNUMBER(A2),A2,VALUE(LEFT(A2,LEN(A2)-3)&":00")+IF(AND(RIGHT(A2,2)="PM",LEFT(A2,2)<>"12"),0.5,0))-"00:15:00",--"00:30:00")

Hope this helps.

Pete

No problem Pete. "This is Magic". It is working absolutely fine. You saved my life mate!

Thanks a ton!

You rock!!!

Best,
Jai

Glad to hear it worked for you, Jai - thanks for feeding back.

Pete

My pleasure Pete! Once again thanks...

I also posted another thread "Cascading formula" but haven't heard from anyone. I was wondering if you could check it out as well but of course at your easy...

Please stay connected! You could also email me on jai.anand@aol.in

Best regards,
Jai

Hi Pete,

I need some more assistance.

Can you help me modify this formula a little further so that all minutes less than 45 get counted in the same hour while the ones greater than or equal to 45 minutes get counted in the next hour. Example: 12:44PM gets converted to 12:00PM but 12:45 gets converted to 1:00pm and so on and so forth...

=CEILING(IF(ISNUMBER(A2),A2,VALUE(LEFT(A2,LEN(A2)-3)&":00")+IF(AND(RIGHT(A2,2)="PM",LEFT(A2,2)<>"12"),0.5,0))-"00:15:00",--"00:30:00")

Thanks in advance!

Jai

You probably want to do this:

=CEILING(IF(ISNUMBER(A2),A2,VALUE(LEFT(A2,LEN(A2)-3)&":00")+IF(AND(RIGHT(A2,2)="PM",LEFT(A2,2)<>"12"),0.5,0))-"00:45:00",--"01:00:00")

Note that I've just changed the final two values.

Hope this helps.

Pete (from a wet Spain)

Awesome...You are simply the best!!!

Thanks a ton!