Obscure Date Conversion Functions?? 2

[fastrunr]

Does anyone know if there is a canned function anywhere to convert a date (or month/day) to a number from 1-365? I know it would be pretty easy to create something to do this, but I don't want to re-invent the wheel if this already exists.

Thanks!

Liz

 

[jebry]

Hi Liz!

I don't think a DayOfYear function exists. You can try the DateDiff function:

DayofYear = DateDiff("d", YourDate, DateSerial(Year(YourDate), 1, 1) + 1

hth


Jeff Bridgham
bridgham@purdue.edu

 

[Golom]

Format([DateField], "y")

Will return a Julian date.

 

[PHV]

And if you want a numeric value instead of text:
DayOfYear: Int(Format([DateField], 'y'))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fastrunr]

Actually, I'm looking for Day of Year (1-365), not Julian Date. I think Jeff's suggestion will help me though.

Thanks everyone!

Liz

 

[PHV]

Liz, Jeff's suggestion needs 3 function calls, Golom's suggestion only one for same result ...

 

[jebry]

Cool,

I didn't know the format function had that available! Learn something all the time around here!



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[fastrunr]

hey - you're right! sometimes I need it spelled out : )

So my next question is why is the range for the "y" format 1-355 instead of 1-365 or 366 as per:


(

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vafctformat.asp)

 

[PHV]

Simply try this:
MsgBox Format(#2004-12-31#, "y")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fastrunr]

Sorry, where do I try that - in the query somewhere?

I *did* try just Format(#2004-12-31#, "y") and it works!
But I'm hung up on the MsgBox part...

Thanks for bearing wtih me!

 

[pbrodsky]

Msgbox is just a quick way to display the result. You use it wherever you need it.

 

[fastrunr]

Thanks!

So why on the msdn site does it list the range for "y" as 1-355? What is it I'm missing?

 

[pbrodsky]

I would guess that whoever wrote the documentation was in a hurry and nobody ever checked it.

 

[jebry]

Sounds like a typo. They have it right in access help.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[pbrodsky]

In access 2002, they have it correct in the Access documentation and incorrect in the VBA documentation.

 

[fastrunr]

Ah...it never occurred to me that it could be a typo. Thanks again everyone. These tips were *exactly* what I was looking for.