Formatting / Time

L

lehigh46

Hi All:

I copied & pasted a sunrise & sunset table from the internet

What I pasted looked like this - 1646
I then added a : to seperate hrs from minuts with a fomula.
I then copied & pasted special - values to another cell which I
formatted - 1:30 PM
It didn't change into 4:46 PM
So, I highlighted the cell and clicked on the formula bar and hit
enter.
It changed into 4:46 PM. Just what I wanted.
Since I'm setting up a sheet for the next 5 years, I dont' want to do
this 3,652 times. How can I do this in mass?

Thanks for your help!

Tom Snyderl





x-- 100 Proof News - http://www.100ProofNews.com
x-- 30+ Days Binary Retention with High Completion
x-- Access to over 1.9 Terabytes per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
 
A

Alan

One way,
If 1646 were in A1, format A1 as text then in another cell,
=LEFT(A1,2)&":"RIGHT(A1,2)3
then copy, paste special values back to the original location,
Regards,
Alan.
 
A

Alan

sorry, typo, not
=LEFT(A1,2)&":"RIGHT(A1,2)3
but
=LEFT(A1,2)&":"RIGHT(A1,2)
Regards,
Alan.
 
L

lehigh46

Alan,

I'm OK with that part which is what i explained in my original post.
What I can't seem to do is simply format the results into 4:46 PM.
I first have to highlight the cell, then click on the formula bar
above the sheet and hit enter.





Hi All:

I copied & pasted a sunrise & sunset table from the internet

What I pasted looked like this - 1646
I then added a : to seperate hrs from minuts with a fomula.
I then copied & pasted special - values to another cell which I
formatted - 1:30 PM
It didn't change into 4:46 PM
So, I highlighted the cell and clicked on the formula bar and hit
enter.
It changed into 4:46 PM. Just what I wanted.
Since I'm setting up a sheet for the next 5 years, I dont' want to do
this 3,652 times. How can I do this in mass?

Thanks for your help!

Tom Snyderl





x-- 100 Proof News - http://www.100ProofNews.com
x-- 30+ Days Binary Retention with High Completion
x-- Access to over 1.9 Terabytes per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD



x-- 100 Proof News - http://www.100ProofNews.com
x-- 30+ Days Binary Retention with High Completion
x-- Access to over 1.9 Terabytes per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto Filter 1
FILE SHARING 1
Time Sum in Excel 1
Text to Numeric 3
Reversing rows of information 1
MARK EVERY FOURTH ROW 3
CALENDAR 3
Email Merge with attachments 2

Top