MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Serial Number/Dates


Posted by Sonja on June 12, 2001 8:43 AM

We receive reports from our home office with the dates as serial numbers (example: 10508)

Does anyone know how to convert these serial numbers to a complete date (mm/dd/yy)?


Posted by Mark W. on June 12, 2001 8:52 AM

What's the base date for these serial numbers?
What date does 10508 represent?

Posted by Sonja on June 12, 2001 9:07 AM

These reports are taken from Business Objects database...binary?

Posted by Mark W. on June 12, 2001 9:27 AM

> These reports are taken from Business Objects database...binary?

I'm afraid this doesn't help me much. I'm not
familiar with this database. You need to contact
your DBA for answers to my questions below.

Posted by ZEN on June 12, 2001 11:12 AM

IT'S A LONG SHOT!!

IF YOU KNOW WHAT THE NUMBER (your eg. 10508) REPRESENTS, SAY 7TH OCT 28, THEN PUT THAT NO. IN A CELL (eg a1) AND FORMAT THE NUMBER TO DATE/ 4-MAR-97, THEN SEE WHAT THE DATE COMES OUT AS (in the case of 10508 it's 7-Oct-28), THEN IN THE NEXT CELL (b2) PUT THE ACTUAL DATE IT REPRESENTS (say 12-Jun-01), IN CELL c1 PUT =B1-A1. IF THE DATA BASE USES THE NORMAL RULE OF LOWEST/HIGHEST NUMBERS FOR DATES, THE DIFFERENCE SHOULD BE THE SAME ALL THE TIME, IN THIS EG. IT'S 26546 AS A GENERAL NUMBER. SO YOU CAN PUT IN THE FORMULA =A1+26546.

AS I SAID IT'S A LONG SHOT BUT GIVE IT GO AND LET ME KNOW IF WORKS, ANY PROBS ASK

GOOD LUCK

ZEN