Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to work with serial number dates and Julian dates in Excel 2000

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

View products that this article applies to.

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

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

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))

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)

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: KB214099, kbhowtomaster, kbformula, kbinfo

↑ Back to the top

Article Info
Article ID : 214099
Revision : 6
Created on : 9/9/2004
Published on : 9/9/2004
Exists online : False
Views : 342