julian date in outlook 2007
badgolferman
have tried one method with the import of an Excel spreadsheet but it
only worked once and I don't seem to be able to make it work this year.
VanguardLH
"A Julian date of 2454115.05486 means that the date and Universal Time
is Sunday January 14, 2007 at 13:18:59.9.
http://en.wikipedia.org/wiki/Julian_Date
You really find that date format of use to you?
badgolferman
VanguardLH
Numbering the days of the year is only a portion of a Julian date. We
can only go by what you said (Julian date), not that you meant to say.
You said Julian date (JD), not Julian day number (JDN) which is just a
portion of JD. The wiki article I mention notes the difference. The
article to which you referred uses the term incorrectly. It shows only
the JDN within an undefined year. It does not supply an actual date as
you requested.
So what you really want to see is the day number within a year (i.e.,
only the JDN of JD), like some desk calendars show. Microsoft has added
a week number to the date navigator pane but not a JDN field in a view
that shows the individual day numbering. There no intrinsic JDN string
shown by Outlook. Googling around shows some folks have figured out how
to use user-defined fields to add the JDN string, as in:
http://www.eggheadcafe.com/software/aspnet/30345296/julian-date-field.aspx
I haven't used or created user-defined fields so someone else will have
to help you with that task.
badgolferman
Okay, I think I got it figured out. This seems to have worked for me.
1. Open Excel and delete any additional sheets.
2. Make two column headers -- A1 = Start Date, B1 = Julian Date.
3. Put the first day of the year under Start Date (1/1/2011).
4. Put the first day of the Julian year under Julian date (1).
5. Select the cell containing the 1/1/2011 and highlight all the way
down to cell 366.
6. From the Editing toolbar select Fill / Series. Ensure Rows, Date,
Day are selected. Step Value =1 and Stop Value is blank. Click OK.
The row should populate with all the dates of the year.
7. Do the same thing with the column you created named Julian Date.
Sequential numbers should fill the cells corresponding to the day of
the year all the way down to 12/31/2011.
8. Save the Excel workbook as a .CSV (Comma Delimited) file.
(julian.csv)
9. Go to Outlook and select the calendar you want populated with
Julian dates.
10. Select File / Import / Import from other program. Click Next.
11. Select Comma Seperated Values (Windows). Click Next.
12. Browse to the file you created and select it. I chose "Do not
import duplicate files". Click Next.
13. In the next window choose the calendar you want populated.
14. In the next window select "Import julian.csv into folder....." and
click on the button that says "Map custom fields".
15. In the left pane drag Start Date over to the right pane and drop it
on Start Date. In the left pane drag Julian Date over to the right
pane and drop it on Subject. Click OK.
16. The next window will be the "Import a File" window from Step 14.
Click Finish.
17. You should see a progress window next performing the requested
action. When finished, the calendar should have days of the year
entered from 1/1/2011 - 12/31/2011.
I haven't made one yet for 2012 which is a leap year. I assume
highlighting all the way to cell 367 is what will be required.
VanguardLH
Might be easier (might not) to define your own custom holiday file
(.hol) with the Julian Day Number. That is, you would define a .hol
file where every day of the year has an event whose title was merely the
JDN value. You might even be able to define a formula in a cell in
Excel that gives you the JDN for every day and create a list of days for
the next 10 years that you would then put into a .hol file and then add
that holiday file to your Outlook calendar.
http://office.microsoft.com/en-us/outlook-help/add-or-delete-holidays-and-custom-events-in-your-calendar-HP001230406.aspx
http://office.microsoft.com/en-us/outlook-help/customize-and-share-the-outlook-calendar-with-new-holidays-and-events-HA001034776.aspx
From what I see looking at the outlook.hol file (a text file), you would
define the 1st column in Excel with incrementing numbers to represent
the JDN value and the 2nd column would be date (yyyy/mm/dd format). I
suppose you could use formulae where the 2nd column was generated
datestamps from a start and end date range and the 1st column would be
incrementing integer values but which reset to 1 when the year changes
in the 2nd column (or just a JDN formula that uses the datestamp in the
2nd column to compute the JDN for whatever is the datestamp in the 2nd
column).
In an Excel spreadsheet, create a 2nd column of datestamps the span a
start and end date range. In the 1st column, find some Excel formula
that someone came up with for converting Gregorian dates to Julian dates
and extracts out just the Julian day number. Many examples can be found
in a search (http://www.google.com/search?q=%2Bexcel+%2Bjulian+%2Bdate).
Instead of a list of JDNs for 1 year, you could create a list for 5, 10,
or more years. Insert a row at the top which is a formula to show the
total number of rows below (this will be the ### value mentioned in the
above articles). Then you export in CSV format which would generate a
.csv file with lines like "JDN,yyyy/mm/dd" (the format needed by the
.hol file). Edit the first line in the .csv file to add a section name
before the ### (total row count) value, like "[Julian day numbers]".
Then append the edited .csv text file onto the .hol text file (copy
outlook.hol+jdn.csv outlook.hol_NEW, ren outlook.hol outlook.hol_OLD,
rename outlook.hol_NEW outlook.hol). Then load Outlook and use its
Holidays function to import just that custom section of your new .hol
file.
Of course, when Microsoft pushes out a new .hol file in an update, your
changes will be lost so keep that Excel spreadsheet around to recreate
the JDN computation. Or just save your modified .csv file to append it
to the next updated .hol file. I don't know if there is a maximum end
date to the entries in a .hol file (i.e., how far out you can go with
the datestamps). I probably wouldn't push it beyond 5 years. Besides,
you'll still have your Excel spreadsheet to compute the next 5 years
worth of JDNs.
Another guy figured on adding a user-defined view to add the JDN value
(http://www.eggheadcafe.com/software/aspnet/30345296/julian-date-field.aspx).
As far as generating 3-digit numbers (when the value is only 1 or 2
digits in length), I would think you could convert the number to a
string, prepend 2 "0" characters to the string, and then trim out the
last 3 characters. "1" would become "001" which trims to "001" whereas
"35" becomes "0035" which trims to "035" and "248" becomes "00248" which
trims to "248". I have created user-defined fields to know if and what
formulae or functions you can use in them.