Converting time format, inserting leading zero and adding +3 hours

**godlev** · 09-07-2011, 06:02 AM

Hi guys i would appreciate if someone help me with formula which can convert AM/PM time to 24hours, then add 3 hours, and insert leading zero only from 1 to 9.

see attachment example click here

**zbor** · 09-07-2011, 06:09 AM

Here, try this:

=TEXT(A1+"3:00", "hh:mm:ss")

**Bob Phillips** · 09-07-2011, 06:10 AM

First use custom format to format the cells as hh:mm:ss.

Then type 03:00:00 in a spare cell and copy it (Ctrl-C)

Select the target cells, goto Paste>PasteSpecial, and click the Add box

Then clear that spare cell.

**inayat** · 09-07-2011, 06:12 AM

Hi,

First thing, please do not use a picture as an attachment. In future please attach the excel file with dummy data. That is a forum rule.

You can easily convert the time zone to 24 hrs.Just select all the cells, then go to format cells. Select Custom and under Type put in this "hh:mm:ss" Select OK and the format will be changed
Formula for adding 3 hours

Please Login or Register  to view this content.

Please let me know if it doesnt work

**zbor** · 09-07-2011, 06:15 AM

Originally Posted by inayat

First thing, please do not use a picture as an attachment. In future please attach the excel file with dummy data. That is a forum rule.

To be more precise, it's not a forum rule. It's an advice how to get your answer quickly.
Because if you post a picture we'll probably ask you to upload example workbook, so you save time...

But sometimes picture helps too...

**daddylonglegs** · 09-07-2011, 06:16 AM

Hello godlev,

I assume your "time" data is actually text, in which case try this formula in D6

=MOD(REPLACE(A6;LEN(A6)-1;0;" ")+"3:00";1)

Now format D6 in required time format, e.g. hh:mm:ss and copy across and down

**inayat** · 09-07-2011, 06:20 AM

Originally Posted by zbor

To be more precise, it's not a forum rule. It's an advice how to get your answer quickly.

Sorry Zbor... Just trying to do my bit for the forum. I had seen many posts wherein the forum gurus had asked for the workbook instead of picture. Sorry again...

**zbor** · 09-07-2011, 06:24 AM

No need for apology.

And posting workbook will be aksed in the future because I (we) surely don't want to spend 15 minutes creating example for a 5 minute solution.
But it's not against the rules to post a picture.
it's just take more time for user to get solution

keep up the good work.

**godlev** · 09-07-2011, 09:47 AM

thank you for the responses ill post example spreadsheet from now on.

but still i didn't got the right formula.

can you please check the example sheet and check how it should convert.

**zbor** · 09-07-2011, 09:52 AM

As it's stored as text use daddylonglegs solution:

**godlev** · 09-07-2011, 10:09 AM

Great thank you all of you - this is the best forum for excel!

**Bob Phillips** · 09-07-2011, 10:41 AM

The answers you are getting assume you want to keep the original table and create a transformed table. If you want to transform the original, you need VBA, or take the approach I gave earlier.