Julian Dates / Calendars

C

Corinne

I would like to set up some sort of formula or macro so when I enter a julian date of 001-366 it automatically changes it to a regular calendar year format - Say I enter "066" - the cell will actually show as 3/6/04 - Anyone familiar with this??
 
R

Ron Rosenfeld

I would like to set up some sort of formula or macro so when I enter a julian date of 001-366 it automatically changes it to a regular calendar year format - Say I enter "066" - the cell will actually show as 3/6/04 - Anyone familiar with this??
Click to expand...

In order to change what the entry cell shows, you will need to use an event
driven macro.

However, you could use one cell for entry, and another to show the result. The
formula would be: =DATE(2004,1,A1)

And you could also use Data/Validation to ensure that only integers from 1 to
366 can be entered.


--ron
 
J

jeff

HI,

Will this work? you enter 66 in A1.

=A1+DATE(YEAR(NOW()),1,1)-1

jeff
-----Original Message-----
I would like to set up some sort of formula or macro so
Click to expand...
when I enter a julian date of 001-366 it automatically
changes it to a regular calendar year format - Say I
enter "066" - the cell will actually show as 3/6/04 -
Anyone familiar with this??
 
R

Ron Rosenfeld

In order to change what the entry cell shows, you will need to use an event
driven macro.

However, you could use one cell for entry, and another to show the result. The
formula would be: =DATE(2004,1,A1)

And you could also use Data/Validation to ensure that only integers from 1 to
366 can be entered.


--ron
Click to expand...


If you always wanted the year to be the current year, you could use the
formula:

=DATE(YEAR(TODAY()),1,A1)


--ron
 
Top