Formula for converting xx/xx/xxxx dates to a number between 1 and

[Rodney Reed]

I need an Excel formula to convert a date (mm/dd/yyyy) to a serial number,
i.e. January 1st = 001, 1st February = 032, 31st and so on to December 31st =
365 . The serial number needs to reset to 001 on Jan 1st of each new year.

Is there a quick way to convert any given date from any given year to this
3-digit day number?

Thanks in advance,
Rodney

 

[Jacob Skaria]

If the date is in excel date format try the below formula with a date in cell
A1

'as text
=TEXT(A1-DATE(YEAR(A1),1,1)+1,"000")

as number
=A1-DATE(YEAR(A1),1,1)+1

If this post helps click Yes

 

[Rick Rothstein]

You can save a mathematical operation like this...

=A1-DATE(YEAR(A1),1,0)

Note to OP... you will probably have to format the cells as General.