I have a spreadsheet that has, what I believe are Julian Dates.

Ex: 20007088
2007090
2007092

these are in a column labled: Date YYYYDDD

Can someone tell me how to format these to MMDDYYYY ?

Thank you!!!! :o))

Right click on the cell, scroll down to format. On the left hand side click
Date. Then on the right hand side choose the format you want the date to
appear as.

"DestinySky" wrote:

I have a spreadsheet that has, what I believe are Julian Dates.

Ex: 20007088
2007090
2007092

these are in a column labled: Date YYYYDDD

Can someone tell me how to format these to MMDDYYYY ?

Thank you!!!! :o))

This will not work... For 2007088 the results are 3/20/95. This is not
correct.

This seems to be an odd format for Julian Dates. I have tried another
formula that won't work.....

anymore ideas????

Thanks!



"Erin Leva" wrote:

Right click on the cell, scroll down to format. On the left hand side click
Date. Then on the right hand side choose the format you want the date to
appear as.

"DestinySky" wrote:

I have a spreadsheet that has, what I believe are Julian Dates.

Ex: 20007088
2007090
2007092

these are in a column labled: Date YYYYDDD

Can someone tell me how to format these to MMDDYYYY ?

Thank you!!!! :o))

Look he

http://www.cpearson.com/excel/jdates.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"DestinySky" wrote in message ...
|I have a spreadsheet that has, what I believe are Julian Dates.
|
| Ex: 20007088
| 2007090
| 2007092
|
| these are in a column labled: Date YYYYDDD
|
| Can someone tell me how to format these to MMDDYYYY ?
|
| Thank you!!!! :o))
|

=DATE(LEFT(A1,4),1,RIGHT(A1,3))


"DestinySky" wrote:

I have a spreadsheet that has, what I believe are Julian Dates.

Ex: 20007088
2007090
2007092

these are in a column labled: Date YYYYDDD

Can someone tell me how to format these to MMDDYYYY ?

Thank you!!!! :o))

=DATE(LEFT(A2,4),1,RIGHT(A2,3))

[I assume that your first example should be 2007088 , not 20007088 ?]
--
David Biddulph

"DestinySky" wrote in message
...
I have a spreadsheet that has, what I believe are Julian Dates.

Ex: 20007088
2007090
2007092

these are in a column labled: Date YYYYDDD

Can someone tell me how to format these to MMDDYYYY ?

Thank you!!!! :o))

Your question is not entirely clear on this, but are you looking to change
those "dates" from the format you see to the format you want in the same
column? I'm going to assume yes for that question. The first thing we need
to know is if those entries are real Excel dates simply formatted to look
like that. Click on any one of those "dates" and look at the Formula Bar. Do
you see the same number as in the cell or do you see a real date?

1) If you see a real date, simply change the Custom Format for the column
from YYYYDDD to MMDDYYYY.

2) If you see the same number, then do you want a simple text value in the
cell or do you want an actual date formatted to look like MMDDYYYY. I'm
going to assume you want a real date formatted to look like MMDDYYYY as that
would be the most flexible for future possible use. Put this formula in an
unused column somewhere (it assumes that your first "Julian Date" is in A2;
change to suit your actual conditions)...

=DATE(LEFT(A2,4),1,RIGHT(A2,3))

Now select the column of date values this formula produced and press Ctrl+C.
Now click into A2 and select Edit/PasteSpecial from Excel's menu bar. Click
the Values option button and then click OK. Next, with the cells still
selected, change the Custom Format for them to mmddyyyy.

Rick


"DestinySky" wrote in message
...
I have a spreadsheet that has, what I believe are Julian Dates.

Ex: 20007088
2007090
2007092

these are in a column labled: Date YYYYDDD

Can someone tell me how to format these to MMDDYYYY ?

Thank you!!!! :o))

Surely a real Excel date formatted as YYYYDDD would show as 2007Thu, not as
2007088 ?
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
Your question is not entirely clear on this, but are you looking to change
those "dates" from the format you see to the format you want in the same
column? I'm going to assume yes for that question. The first thing we need
to know is if those entries are real Excel dates simply formatted to look
like that. Click on any one of those "dates" and look at the Formula Bar.
Do you see the same number as in the cell or do you see a real date?

1) If you see a real date, simply change the Custom Format for the column
from YYYYDDD to MMDDYYYY.

2) If you see the same number, then do you want a simple text value in the
cell or do you want an actual date formatted to look like MMDDYYYY. I'm
going to assume you want a real date formatted to look like MMDDYYYY as
that would be the most flexible for future possible use. Put this formula
in an unused column somewhere (it assumes that your first "Julian Date" is
in A2; change to suit your actual conditions)...

=DATE(LEFT(A2,4),1,RIGHT(A2,3))

Now select the column of date values this formula produced and press
Ctrl+C. Now click into A2 and select Edit/PasteSpecial from Excel's menu
bar. Click the Values option button and then click OK. Next, with the
cells still selected, change the Custom Format for them to mmddyyyy.

Rick


"DestinySky" wrote in message
...
I have a spreadsheet that has, what I believe are Julian Dates.

Ex: 20007088
2007090
2007092

these are in a column labled: Date YYYYDDD

Can someone tell me how to format these to MMDDYYYY ?

Thank you!!!! :o))

Surely a real Excel date formatted as YYYYDDD would show
as 2007Thu, not as 2007088 ?

LOL ... Surely you are right.

Rick