This article explains how to convert serial number dates
to "Julian dates" and how to convert Julian dates to serial number
dates in Microsoft Excel 2000. Julian dates refer to the number of days from
the first of the year and the number of days until the end of the year as
printed on most desktop calendars.
Many government agencies and contractors require the use of Julian dates.
The following are examples of Julian dates:
�� Date��������� Julian date
�� -------------------------
�� Jan-01-1999�� 99001
�� Jan-10-1999�� 99010
�������������������������
The following examples show how to work with serial number
dates and Julian dates.
Use the following Microsoft Excel 2000 formula to convert
a serial number date to a Julian date
�� =TEXT(Standard_Date,"yy")&TEXT((Standard_Date-
����� DATEVALUE("1/1/"&TEXT(Standard_Date,"yy"))+1),"000")
Better version
=VALUE(TEXT((D2-DATEVALUE("1/1/"&TEXT(D2,"yy"))+1),"000")) [d2 is a data cell]
where Standard_Date is the defined name that
you gave to the cell that contains the date to be converted. You must enter
the entire formula in a single cell.
back to the
top
The following formula converts a Julian date to a standard
serial number date in Excel (the formula assumes that your date is in the
20th century):
�� =DATE(1900+INT(Julian_Date/1000),1,MOD(Julian_Date,1000))
�������������������������
To calculate the number of days since January 1 of the
current year, use the following formula:
�� =INT(((NOW()/365.255)-(YEAR(NOW())-1900))*365.255)
�������������������������
To calculate the number of days left in the current year,
use the following formula:
�� =(DATE(1+YEAR(NOW()),1,1)-(DATE(YEAR(NOW()),1,1)))-
�������� TRUNC(((NOW()/365.255)-(YEAR(NOW())-1900))*365.255)