Date Conversion

[Port Man]

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.
--

 

[JE McGimpsey]

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

 

[Port Man]

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?

--

 

[JE McGimpsey]

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)?

 

[Port Man]

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.

--

 

[JE McGimpsey]

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).

 

[David Biddulph]

"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.