Need a formula in excel and c# to convert data in "yyyymmdd" format to julian date format "yyJJJ"? Where "JJJ" is the Julian date of day submitted. Is JJJ a number from 1 to 365 or 999 or 366? I don't think its 366, but we do have leap year so it could be leap year.
In Excel I'm trying to use the following:
=Year() & DAYS360("20110101","20110930")
In C# I'm using now() to get the current date. But I'm want the number of days between Jan 1st, and current date. Not sure if this is the correct prototype formula:
Prepend Two digit year format to the following
Number of Days between "20110101" and "20110930" in "YY" format
Today's date is 27-Oct-2022 (UTC). Today's Julian Date is 22300 .
In a blank cell, type this formula =TEXT(A1,"yy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000") and press Enter key, if you need you can apply this formula to a range by dragging the auto fill handle. Tip: A1 is the calendar date you want to convert to Julian date.
Enter today as a Julian date To do this task, use the TEXT, TODAY, and DATEVALUE functions.
Instead of handling this yourself in C#, you could simply use the JulianCalendar class
And to get today in the Julian calendar, you could do:
JulianCalendar calendar = new JulianCalendar();
var today=DateTime.Today;
var dateInJulian = calendar.ToDateTime(today.Year, today.Month, today.Day, today.Hour, today.Minute, today.Second, today.Millisecond);
EDIT:
And I realized I didn't help you with a good way to get a result in the YYJJJ format you were looking for, it's quite simple:
var stringResult=string.Format("{0}{1}", dateInJulian.ToString("yy"), dateInJulian.DayOfYear);
Sadly, I don't think there's an equivalent of dateTime.ToString("JJJ");
that will give the day of the year, hence my little string.Format()
work-around, but it works just as well!
EDIT2:
So Phoog educated me a bit below in the comments, and pointed out that you weren't looking for today in the Julian Calendar, which is what the above code would give in respect to the JulianCalendar class. Rather, it appears you're looking for the Ordinal date (this piece added for future reader clarification). I'm going to leave the above so that someone doesn't make the same mistake that I did. What you really want, I believe, is the code from the first edit but without the JulianCalendar business, so:
var dateToConvert = DateTime.Today // Or any other date you want!
var stringResult=string.Format("{0}{1}", dateToConvert.ToString("yy"), dateToConvert.DayOfYear);
According to these articles, you should be using the term "Ordinal date" rather than "Julian date":
http://en.wikipedia.org/wiki/Ordinal_date
http://en.wikipedia.org/wiki/Julian_day
According to the first, the ISO standard for ordinal date specifies a 4-digit year, and that the range is from 1 to 366, so January 1st is 1.
This of course means that dates between March and December will have two possible values depending on whether the date falls in a leap year.
Bala's solution (with the + 1
option given in the comments) will therefore suffice.
EDIT: The DateTime
type's own DayOfYear property, as Jim Mischel pointed out, is simpler and probably faster.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With