Julian date question

[Jessica]

Hello All,


Out of the three reports that I have I need one of them to display a
julian date which I already have set up that counts in February 29th
every year. Here is the function I have so far and was wondering what I
needed to add to it to make it happen.

=Format(DatePart("y",Date()),"000") & Right(Format(Date(),"yy"),1)

I.e.

February 28th = 0595
February 29th = 0605
March 1st = 0615


TIA,
Jess

 

[Wayne Morgan]

What you have won't count in Feb 29 each year, because Feb 29 doesn't exist
in each year. Are you wanting to pretend that there are 366 days in the year
whether there are or not? If so, try

=IIf(IsDate(DateSerial(Year(Date()), 2, 29)),
Format(DatePart("y",Date()),"000") & Right(Format(Date(),"yy"),1),
IIf(Date()>=DateSerial(Year(Date()), 3, 1),
Format(DatePart("y",Date())+1,"000") & Right(Format(Date(),"yy"),1),
Format(DatePart("y",Date()),"000") & Right(Format(Date(),"yy"),1)))

This checks to see if Feb 29 exists, if so, then what you have will work. If
not, then is the date Mar 1 or later. If so, add one day. You will wind up
skipping day 60 during years that Feb 29 doesn't exist, so Feb 28 will be 59
and Mar 1 will be 61.

 

[Jessica]

Hi Wayne,

I tried that code and then changed my system clock to March 1st which
should have shown 0615 on my report but still showed 0605. Did I do
something wrong?

Thanks,
Jess

 

[Jessica]

Hi Wayne,

I just figured it out I put the +1 after the "000"

IIf(Date()>=DateSerial(Year(Date()), 3, 1),
Format(DatePart("y",Date()),"000")+1 & Right(Format(Date(),"yy"),1)

Thank you for your help I truly appreciate it,
Jess

 

[Jessica]

Ok now I can't get it to work either way. What am I doing wrong???

Jess

 

[Wayne Morgan]

You're not doing anything wrong. I just forgot a little item about
DateSerial. If you give it a value that is incorrect, it compensates for it.
So when given DateSerial(2005,2,29) it makes it into a date. Since Feb only
has 28 days, it adds one day and gives Mar 1st, which is a date, so the
IsDate is always true.

Adjusted statement:
= IIf(IsDate("#2/29/" & Year(Date()) & "#"), Format(DatePart("y", Date()),
"000") & Right(Format(Date(), "yy"), 1), IIf(Date()>=
DateSerial(Year(Date()), 3, 1), Format(DatePart("y", Date()) + 1, "000") &
Right(Format(Date(), "yy"), 1), Format(DatePart("y", Date()), "000") &
Right(Format(Date(), "yy"), 1)))

 

[Jessica]

Thanks Wayne Works like a charm