Hello
I have a report that contains spot times for a tv campaign in the following format:
Time
910
1015
1056
1156
1230
1256
1310
1328
1330
1350
I need to have these times represented in the hh:mm AM/PM format so I can chart them but I have no idea how to convert them. Everything I have tried has not worked.
Any ideas?
Many thanks in advance
In an adjacent column use formula:
=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0)
where A1 is first time to convert... and copy down column needs to be formatted as time, through Format|Cells >> Number tab
You can then overwrite original by copying the formula column and paste special >> Values over the original.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
You could use this formula....
=TEXT(A1,"00\:00")+0
format as time
or
B1: =0+TEXT(A1,"00\:00")
(edit: dll beat me to it - just going to leave mine this once just to provee I came up with the same approach as him ... hooray!)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks