Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Conversion
1. I have cells that have dates in the format of yyyymmdd and I want
to change them into mmddyyyy. 2. Also, I want to make another column that will have the mmddyyyy converted to unix date format. 3. Text in some cells are all in UPPERCASE and I want to convert it to lowercase. Is this possbile? Thank you. -- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Conversion
In article ,
"Port Man" wrote: 1. I have cells that have dates in the format of yyyymmdd and I want to change them into mmddyyyy. One way: Select your data, choose Data/Text to Columns, Next, Next, select YMD from the date dropdown. Click Finish. Format as mmddyyyy. 2. Also, I want to make another column that will have the mmddyyyy converted to unix date format. One way: With date in A1: =(A1-DATE(1970,1,1))*86400 3. Text in some cells are all in UPPERCASE and I want to convert it to lowercase. Is this possbile? See http://www.mvps.org/dmcritchie/excel/proper.htm#lower |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Conversion
Thanks, I was able to get all of it, except the unix time stamp.
Using the formula =(A1-DATE(1970,1,1))*86400 and converting the date 8/6/2004, it gives 1091750400 which translates to 08 / 05 / 04 @ 5:00pm What am I doing wrong? -- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Conversion
Why do you think that 1091750400 translates to 8/5/2004 17:00?
Are you making a correction for GMT? (e.g., -7 hours for MST or PDT)? In article , "Port Man" wrote: Thanks, I was able to get all of it, except the unix time stamp. Using the formula =(A1-DATE(1970,1,1))*86400 and converting the date 8/6/2004, it gives 1091750400 which translates to 08 / 05 / 04 @ 5:00pm What am I doing wrong? -- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Conversion
No, I'm not making a correction for GMT. Am I supposed to? If so, how?
When I put the converted unix time of 1091750400 into an online time converter, it pulls up that date. What am I doing wrong? Thanks, JE. -- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Conversion
In article ,
"Port Man" wrote: No, I'm not making a correction for GMT. Am I supposed to? I don't know - what are you trying to accomplish? If so, how? One way would be to add 3600 for every hour of difference between your local time and GMT. You may also have to adjust for daylight savings time, depending on what country and state you're in. Note that the rules for starting and ending DST changed this year. When I put the converted unix time of 1091750400 into an online time converter, it pulls up that date. Sounds like the online converter is making an adjustment for time zone. Are you in the same time zone as the online converter assumes? What am I doing wrong? Nothing, but you need to specify your requirements fully. XL only knows about dates and times as floating point numbers, and displayed times assume a local reference rather than absolute (GMT). |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date Conversion
"UNIX time 1091750400 is 08/06/2004 12:00am GMT."
according to http://soft.zoneo.net/Unixtime/index.php At least one on-line converter does default to the user's local time unless specifically asked to return the number in GMT. -- David Biddulph "JE McGimpsey" wrote in message ... In article , "Port Man" wrote: No, I'm not making a correction for GMT. Am I supposed to? I don't know - what are you trying to accomplish? If so, how? One way would be to add 3600 for every hour of difference between your local time and GMT. You may also have to adjust for daylight savings time, depending on what country and state you're in. Note that the rules for starting and ending DST changed this year. When I put the converted unix time of 1091750400 into an online time converter, it pulls up that date. Sounds like the online converter is making an adjustment for time zone. Are you in the same time zone as the online converter assumes? What am I doing wrong? Nothing, but you need to specify your requirements fully. XL only knows about dates and times as floating point numbers, and displayed times assume a local reference rather than absolute (GMT). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date conversion | Excel Discussion (Misc queries) | |||
Date Conversion | Excel Worksheet Functions | |||
Date conversion | Excel Worksheet Functions | |||
Date Conversion | Excel Worksheet Functions | |||
date conversion | Excel Worksheet Functions |