Calculating dates, displaying minimums


Tip
Let me start off with a big "thank you" this month to all you readers. Your constant, gentle bombardment of questions, suggestions, and tips has taught me an enormous amount about spreadsheets and is the source of the information I pass along every month.
Here's a jewel submitted by Shane Devenshire:
Regular spreadsheet date serial numbers are integers that represent the dates between January 1, 1900 (day 1) and December 31, 2099 (day 73,050). September 1, 1996, then, is day 35,309. Many businesses, however, need to know a date's number within a year: While January 1 is day 1, September 1 is either day 244 or day 245, depending upon whether it's a leap year, and so on.
This Excel formula converts a date serial number in cell A1 into the day of the year: =int(mod(A1-1,365.25)+1). The mod() function calculates the remainder (modulus) when one number is divided by another, so dividing a date value by 365.25 gives the number of days since the beginning of the current year. (Subtracting one from the year value and adding it back to the modulus prevents the last day of the year from appearing as zero.)
If you're using Quattro Pro or 1-2-3, enter the following formula: @int(@mod(A1- 1,365.25)+1). If you're working in 1-2-3 release 5, you can enter this special calendar function to return the number of the day within the year: @dateinfo(A1,12).
- Richard Scoville

Category: Spreadsheet
Issue: Nov 1996
Pages: 164

These Web pages are produced by Australian PC World © 1997 IDG Communications