Convert Datetime to Julian Date

[bbrendan]

Hi im hoping someone can help me here please.

I have a datetime picker which returns a selected date like:

01/12/2004
I need to convert this to a julian number.

In excel its easy you just change the format to number an boom you get
38322

So how would I change it when I select a date.

Its just that all our dates are stored in Julian dates

thanks

 

[Nelviticus]

If you can split your date up into year, month and day you can try:

DateSerial(year, month, day)

Does that help?

Nelviticus

 

[PHV]

What about Format(theDate, "y") ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi bbrendan,

There are several ways to get the Excel internal date representation, but it is not a Julian Date; here's one ..

[blue]

Format([i]YourDate[/i], "00000")

[/blue]
Note, however, that this will round the date off, so if you have a time attached (which you won't from your date picker, but might have in the more general case), you will need to do something like ..

[blue]

Format(DateValue([i]YourDate[/i]), "00000")

[/blue]
To get the Julian Date, you could try ..

[blue]

Format([i]YourDate[/i], "yy") & Right("00" & Format([i]YourDate[/i], "y"), 3)

[/blue]
There is probably an easier way but it's not springing readily to mind.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[bbrendan]

hi all

thanks for your help on this. Tony's first suggestion was right on!

Format(YourDate, "00000")

cheers

 

[Bowers74]

To avoid the possible Rounding of the date value (with a time attached) use:

Application.WorksheetFunction.RoundDown([A1], -1)

;-)

P.S. The Date format you want is not a Julian date (to my knowledge), it is simply an Excel Date Serial number, based on January 1, 1900 being day 1 and today being day 38120.

The only Julian Date format that I know of is "yyddd" (or the last two digets of the year and the ordinal day number for today) OR Today = 04134

I could be wrong though ;-)


Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here

 

[macropod]

Excel's dates are NOT stored as julian day numbers or so-called julian dates - they are just serial numbers, nominally starting from 1/1/1900 (PCs) or 1/1/1904 (Macs).

A Julian day number is a count of the number of days since a nominal date of 1 January 4713 BC under the Julian calendar (ie the calendar used in most Western contries up until 1582). Today's (14 May 2004 (Gregorian) or 1 May 2004 (Julian)) Julian day number is 2,453,140.

The so-called Julian dates have nothing to do with Julian day numbers or (in most cases) the Julian calendar. The term is usually applied to the number of the day of the year in a Gregorian calendar, and tends to be expressed as yynnn, where yy is the year of the relevant century (ie no Y2K compliance) and nnn is the day number in that year. Under this scheme, today's Julian date would be 04135 if you're using a Gregorian calendar, or 04122 if you're using a Julian calendar.

If you want some algorithms for converting between these different conventions, see the Word file at:

http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=wrd&Number=249902

Although this document uses Word fields to do the calculations, the algorithms can be used elsewhere.

Cheers

 

[KKUB]

I have found the following formula works globally to convert Julian dates to Standard dates in excel.
=Date(1900+(LEFT(TEXT(Your date,"000000"),3)),1,RIGHT(Your Date,3))


KKUB