Question posted 2011 · +5 upvotes
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
Accepted answer +9 upvotes
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);
3 code variants in this answer
- Variant 1 — 3 lines, starts with
JulianCalendar calendar = new JulianCalendar(); - Variant 2 — 1 lines, starts with
var stringResult=string.Format("{0}{1}", dateInJulian.ToStr… - Variant 3 — 2 lines, starts with
var dateToConvert = DateTime.Today // Or any other date you…
Top excel Q&A (6)
- Shortcut to Apply a Formula to an Entire Column in Excel +335 (2011)
- How should I escape commas and speech marks in CSV files so they work in Excel? +136 (2012)
- Convert xlsx to csv in linux command line +96 (2012)
- How to create a link inside a cell using EPPlus +50 (2011)
- IF statement: how to leave cell blank if condition is false ("" does not work) +44 (2013)
- T-SQL: Export to new Excel file +44 (2012)
excel solutions on this site
.