Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formatting / Time
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 |
#2
|
|||
|
|||
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. "lehigh46" wrote in message ... 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 |
#3
|
|||
|
|||
sorry, typo, not
=LEFT(A1,2)&":"RIGHT(A1,2)3 but =LEFT(A1,2)&":"RIGHT(A1,2) Regards, Alan. "lehigh46" wrote in message ... 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 |
#4
|
|||
|
|||
On Mon, 25 Jul 2005 13:14:51 -0400, lehigh46
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time formatting | Excel Discussion (Misc queries) | |||
Date and time formatting | Excel Discussion (Misc queries) | |||
adding time zones to formatting | Excel Worksheet Functions | |||
Time & Date Formatting | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |