Julian Dates / Calendars

[Guest]

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

 

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

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

 

[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

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

 

[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

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

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


--ron