Convert Dates

G

Guest

How do you convert a Julian date with a 4 digit year into a standard calendar
date?
 
G

Guest

This formula did not work. I have a date of 20050609 for June 9, 2005 but
when I put in the formula it gave me this date: 8/31/2021
 
G

Guest

This formula did not work. I have a date of 20050609 for June 9, 2005 but
when I put in the formula it gave me this date: 8/31/2021
 
B

Bob Phillips

That is not a Julian date. You want

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
B

Bob Phillips

That is not a Julian date. You want

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
R

Roger Govier

Hi Jo Anna

With your value in A1, try
=DATE(--LEFT(A1,4),--MID(A1,5,2),--(RIGHT(A1,2)))

What you have is not a Julian date, that is why the formula on Chip's
site does not work
 
R

Roger Govier

Hi Jo Anna

With your value in A1, try
=DATE(--LEFT(A1,4),--MID(A1,5,2),--(RIGHT(A1,2)))

What you have is not a Julian date, that is why the formula on Chip's
site does not work
 
G

Gord Dibben

Joanna

Bob and Roger supplied formulas.

If you do not want to deal with formulas use...

Data>Text to Columns>Next>Next>Column Data Format>Date>YMD and Finish.


Gord Dibben MS Excel MVP
 
G

Gord Dibben

Joanna

Bob and Roger supplied formulas.

If you do not want to deal with formulas use...

Data>Text to Columns>Next>Next>Column Data Format>Date>YMD and Finish.


Gord Dibben MS Excel MVP
 

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

converting julian day and year to a date? 2
changing text to date format 8
julian dates in Exel 1
Convert a julian gregorian date code into a regular date 3
Moon phases and julian dates 0
How do I convert Julian date to fiscal date 1
import excel 2003 dates & notes to Julian calendar format? 2
Formula for converting xx/xx/xxxx dates to a number between 1 and 2

Top