| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for converting xx/xx/xxxx dates to a number between 1 and
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for converting xx/xx/xxxx dates to a number between 1 and
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 --------------- Jacob Skaria "Rodney Reed" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula for converting xx/xx/xxxx dates to a number between 1 and
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. -- Rick (MVP - Excel) "Jacob Skaria" wrote in message ... 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 --------------- Jacob Skaria "Rodney Reed" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dates converting to MS serial number | Excel Discussion (Misc queries) | |||
| How do I changet a text telephone number (xxx) xxx-xxxx to xxxxxx. | Excel Worksheet Functions | |||
| Format a number (i.e. 1111111111111111) to show XXXX-XXXX-1111-11 | Excel Discussion (Misc queries) | |||
| change dots to dashes in an account format (xx.xxxx.xxxx) to xx-xx | Excel Discussion (Misc queries) | |||
| Converting dates to a number | New Users to Excel | |||
Hybrid Mode
