Microsoft KB Archive/214099

= How to work with serial number dates and Julian dates in Excel 2000 =

Article ID: 214099

Article Last Modified on 9/9/2004

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214099





For a Microsoft Excel 98 and earlier version of this article, see 95078.

IN THIS TASK
SUMMARY Date Conversion Formulas
 * To Convert a Serial Number Date to a Julian Date
 * To Convert a Julian Date to a Serial Number Date
 * To Calculate the Number of Days Since January 1st
 * To Calculate the Number of Days Left in the Current Year



SUMMARY
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.

Examples of Julian Dates
The following are examples of Julian dates:   Date          Julian date -  Jan-01-1999   99001 Jan-10-1999  99010 back to the top

Date Conversion Formulas
The following examples show how to work with serial number dates and Julian dates.

To Convert a Serial Number Date to a Julian Date
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") where  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

To Convert a Julian Date to a Serial Number Date
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)) back to the top

To Calculate the Number of Days Since January 1
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) back to the top

To Calculate the Number of Days Left in the Current Year
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) back to the top

Keywords: kbhowtomaster kbinfo kbformula KB214099

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.