Julian Date conversion

[Reggie]

Hi and TIA! I posted this to another grroup but then saw this one and it
looks more suited to my question. I've researched and tried many many
conversion functions but none give me the results. One Example


Public Function JDatetoDate(MyDate As Integer) As Date

JDatetoDate = DateAdd("d", Right(MyDate, 3) - 1, "01/01/" &
Left(year(Now()), 3) & Left(MyDate, 1))

End Function

?JDatetoDate(8044) returned 2/13/2018 expected 2/13/2008
?JDatetoDate(0044) returned 2/13/2014 expected 2/13/2010

Any ideas. Basically want to convert a 4 digit julian to a date/time and
then I will add 1 minute to it. Thanks!

 

[John W. Vinson]

Hi and TIA! I posted this to another grroup but then saw this one and it
looks more suited to my question. I've researched and tried many many
conversion functions but none give me the results. One Example


Public Function JDatetoDate(MyDate As Integer) As Date

JDatetoDate = DateAdd("d", Right(MyDate, 3) - 1, "01/01/" &
Left(year(Now()), 3) & Left(MyDate, 1))

End Function

?JDatetoDate(8044) returned 2/13/2018 expected 2/13/2008
?JDatetoDate(0044) returned 2/13/2014 expected 2/13/2010

Any ideas. Basically want to convert a 4 digit julian to a date/time and
then I will add 1 minute to it. Thanks!

Answered in m.p.a.

On rereading here though I realized why your 0044 wasn't working. MyDate is an
integer, and an integer has no leading zeros. Left(MyDate, 1) is "4" if MyDate
is 44 - or 0044 - or 000000000000000044, since those are all the same number.

And as written the function will return dates between 2010 and 2019, the
current decade.

 

[Reggie]

Thanks much John. Getting closer but still not getting results. Here's
what I'm getting.

JDatetoDate = DateSerial(10 * (year(Date) \ 10) - 10 + Val(Left([MyDate],
1)), 1, Val(Right([MyDate], 3)))

?JDatetoDate(0044)
2/13/2004

?JDatetoDate(1044)
2/13/2001

Thanks for your time!

 

[John W. Vinson]

Thanks much John. Getting closer but still not getting results. Here's
what I'm getting.

JDatetoDate = DateSerial(10 * (year(Date) \ 10) - 10 + Val(Left([MyDate],
1)), 1, Val(Right([MyDate], 3)))

?JDatetoDate(0044)
2/13/2004

?JDatetoDate(1044)
2/13/2001

Thanks for your time!

The problem is that you're still assuming that Left([MyDate], 1) will be 0.

If MyDate is a number field that value will NEVER be 0.

Numbers *do not have leading zeroes*.

Left(44, 1) is 4; Left (0044, 1) is 4; Left(00000000000000000000044, 1) is
also 4.

Numbers are numbers; strings are strings.

 

[John W. Vinson]

Thanks much John. Getting closer but still not getting results. Here's
what I'm getting.

JDatetoDate = DateSerial(10 * (year(Date) \ 10) - 10 + Val(Left([MyDate],
1)), 1, Val(Right([MyDate], 3)))

?JDatetoDate(0044)
2/13/2004

?JDatetoDate(1044)
2/13/2001

Ok... sorry about my outburst earlier.

Try

JDatetoDate = DateSerial(10 * (Year(Date()) \ 10) - 10 + [MyDate] \ 1000), 1,
[MyDate] MOD 1000)

I.e. use math functions entirely, not string functions.

This will give results in the *PREVIOUS* decade, no matter when the query is
run; i.e. in 2019 it will give dates between 2000 and 2009. You still have not
indicated how you want the decade to be determined, and if you have both
future and past dates, it's ambiguous; if you run the query in 2015, do you
want 0044 to be 2000, or 2010, or 2020?

 

[Reggie]

Thanks much John. Getting closer but still not getting results. Here's
what I'm getting.

JDatetoDate = DateSerial(10 * (year(Date) \ 10) - 10 + Val(Left([MyDate],
1)), 1, Val(Right([MyDate], 3)))

?JDatetoDate(0044)
2/13/2004

?JDatetoDate(1044)
2/13/2001

Ok... sorry about my outburst earlier.

Try

JDatetoDate = DateSerial(10 * (Year(Date()) \ 10) - 10 + [MyDate] \ 1000),
1,
[MyDate] MOD 1000)

I.e. use math functions entirely, not string functions.

This will give results in the *PREVIOUS* decade, no matter when the query
is
run; i.e. in 2019 it will give dates between 2000 and 2009. You still have
not
indicated how you want the decade to be determined, and if you have both
future and past dates, it's ambiguous; if you run the query in 2015, do
you
want 0044 to be 2000, or 2010, or 2020?

John, No worries. I see what your saying. My original thought was to
always use the current decade. So if I ran it in 2015 it would use 2010
decade, but after review I can see where this could be a problem. For
example if I received a doc marked 0004 (01/04/10) and I covert it in my
app on 12/31/2019 alls good if using the current decade idea, however if I
import the doc and convert it on 01/01/2020 it would mark the doc
incorrectly using the 2020 decade. Don't know my friend how to handle this.
Its very common for me to get a document marked 0364 (12/30/2010) but I
don't import it until say 01/04/2020. This is a string(text) field so If I
could run a procedure to see if the first character and if so multiply the
result by 10yrs (If Left(mydate,1)=0 then Jdatetodate = Jdatetodate * 10).
I'll try it out and let you know if I come up with anything. Thanks again
for your time. Take care.

 

[Reggie]

John, Think I got it. Like I said this is a string so I enclosed the
variable in quotes and used the below formulas and alls good. Both of these
formulas worked. Thanks again.

Public Function JDatetoDate(MyDate As String) As Date
JDatetoDate = DateAdd("d", Right(MyDate, 3) - 1, "01/01/" &
Left(year(Now()), 3) & Left(MyDate, 1))
'JDatetoDate = DateSerial(10 * (year(Date) \ 10) + Val(Left([MyDate],
1)), 1, Val(Right([MyDate], 3)))
End Function

?JDatetoDate("0001")
1/1/2010


--

Reggie

Thanks much John. Getting closer but still not getting results. Here's
what I'm getting.

JDatetoDate = DateSerial(10 * (year(Date) \ 10) - 10 + Val(Left([MyDate],
1)), 1, Val(Right([MyDate], 3)))

?JDatetoDate(0044)
2/13/2004

?JDatetoDate(1044)
2/13/2001

Ok... sorry about my outburst earlier.

Try

JDatetoDate = DateSerial(10 * (Year(Date()) \ 10) - 10 + [MyDate] \ 1000),
1,
[MyDate] MOD 1000)

I.e. use math functions entirely, not string functions.

This will give results in the *PREVIOUS* decade, no matter when the query
is
run; i.e. in 2019 it will give dates between 2000 and 2009. You still have
not
indicated how you want the decade to be determined, and if you have both
future and past dates, it's ambiguous; if you run the query in 2015, do
you
want 0044 to be 2000, or 2010, or 2020?

 

[Reggie]

John, Was wondering is it possible now that I have a date to change it to a
date/time format and add 1 minute to. My client uses this for calculating
and only gets credit for the day if the time is at least 12:01am

Thanks!

--

Reggie

John, Think I got it. Like I said this is a string so I enclosed the
variable in quotes and used the below formulas and alls good. Both of
these formulas worked. Thanks again.

Public Function JDatetoDate(MyDate As String) As Date
JDatetoDate = DateAdd("d", Right(MyDate, 3) - 1, "01/01/" &
Left(year(Now()), 3) & Left(MyDate, 1))
'JDatetoDate = DateSerial(10 * (year(Date) \ 10) + Val(Left([MyDate],
1)), 1, Val(Right([MyDate], 3)))
End Function

?JDatetoDate("0001")
1/1/2010


--

Reggie

Thanks much John. Getting closer but still not getting results. Here's
what I'm getting.

JDatetoDate = DateSerial(10 * (year(Date) \ 10) - 10 + Val(Left([MyDate],
1)), 1, Val(Right([MyDate], 3)))

?JDatetoDate(0044)
2/13/2004

?JDatetoDate(1044)
2/13/2001

Ok... sorry about my outburst earlier.

Try

JDatetoDate = DateSerial(10 * (Year(Date()) \ 10) - 10 + [MyDate] \
1000), 1,
[MyDate] MOD 1000)

I.e. use math functions entirely, not string functions.

This will give results in the *PREVIOUS* decade, no matter when the query
is
run; i.e. in 2019 it will give dates between 2000 and 2009. You still
have not
indicated how you want the decade to be determined, and if you have both
future and past dates, it's ambiguous; if you run the query in 2015, do
you
want 0044 to be 2000, or 2010, or 2020?

 

[John Spencer]

Since you have a date use the DateAdd function to add on minute to the date

DateAdd("n",1,JDatetoDate([MyDate]))

You could also do that in the function, but I would not add that complication
to the function since you might want just the date for other reasons.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Reggie]

Very nice and thanks once again for your quick response and your time!

--

Reggie

Since you have a date use the DateAdd function to add on minute to the
date

DateAdd("n",1,JDatetoDate([MyDate]))

You could also do that in the function, but I would not add that
complication to the function since you might want just the date for other
reasons.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John, Was wondering is it possible now that I have a date to change it
to a date/time format and add 1 minute to. My client uses this for
calculating and only gets credit for the day if the time is at least
12:01am

Thanks!