Adding of the 9 Hours in Time (In Text Format) with correct

[thilag]

Hello Friends

Version : MS Excel 2010,
File Name : <time addition with AM and PM.xlsx>

From In time I want to add the 9 hours and accordingly AM / PM has to shown correctly.

Also the Afternoon time after 11:59 AM should be 12.00 PM and the midnight after 11:59 PM should be 00:00 AM.

I did a formula myself, please check the same (Correct it also) and tell me if any errors.

Also without adding any helper rows, helper columns, helper cells is it possible to reduce / simply the formula ?

thanks in advance

thilag

[martindwilson]

several ways
=SUBSTITUTE(A1,".",":")+9/24
format cell as time hh:mm am/pm
or text value
=TEXT(SUBSTITUTE(A1,".",":")+9/24,"hh:mm am/pm")
which will give 09:30 AM
to get back to your original format
=SUBSTITUTE(TEXT(SUBSTITUTE(A1,".",":")+9/24,"hh:mm am/pm"),":",".")
which gives
09.30 AM

[thilag]

Hello martindwilson

My sincere thanks to you, Just very simple formulas you did it with the time formats.

Only one thing 00:35 AM in this formulas 12:35 AM, Is it possible to plot 00:35 AM (As looks my time in Text format)

thanks

thilag

[martindwilson]

=SUBSTITUTE(IF(TEXT(SUBSTITUTE(A1,".",":")+9/24,"am/pm")="AM",SUBSTITUTE(TEXT(SUBSTITUTE(A1,".",":")+9/24,"hh:mm am/pm"),"12:","00:"),TEXT(SUBSTITUTE(A1,".",":")+9/24,"hh:mm am/pm")),":",".")

[thilag]

Hello martindwilson

The above formula works for 00.25 AM also.

Thanks for this job.

thilag