Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a month-date format to day number of the year?
I have a list of dates in the format Mar-15. I want to convert these to the
day number of the year. In a non-leap year, Mar-15 would be 74 (the 74th day of the year) and Dec-31 would be 365. Have not yet found a way to do this in Excel. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a month-date format to day number of the year?
Here are a couple ideas....
If all of the dates are from the same year (assuming 2007) AND you want to convert them to the day-of-the-year Then.... Change the number format of the dates to one of the regular number formats Put 12/31/2006 in a blank cell Copy that cell Select the dates <edit<paste special Check: Subtract Click [OK] OR....if you want a formula to return the day-of-the-year With A1: (a date) B1: =A1-DATE(YEAR(A1),1,0) (formatted as a regular number) I can't think of a number format that would return the day-of-the-year, though. Does that help? *********** Regards, Ron XL2002, WinXP "Larry" wrote: I have a list of dates in the format Mar-15. I want to convert these to the day number of the year. In a non-leap year, Mar-15 would be 74 (the 74th day of the year) and Dec-31 would be 365. Have not yet found a way to do this in Excel. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a month-date format to day number of the year
Ron,
I tried your second method (the first wasn't quite clear to me), and it worked perfectly. Molto grazie! "Ron Coderre" wrote: Here are a couple ideas.... If all of the dates are from the same year (assuming 2007) AND you want to convert them to the day-of-the-year Then.... Change the number format of the dates to one of the regular number formats Put 12/31/2006 in a blank cell Copy that cell Select the dates <edit<paste special Check: Subtract Click [OK] OR....if you want a formula to return the day-of-the-year With A1: (a date) B1: =A1-DATE(YEAR(A1),1,0) (formatted as a regular number) I can't think of a number format that would return the day-of-the-year, though. Does that help? *********** Regards, Ron XL2002, WinXP "Larry" wrote: I have a list of dates in the format Mar-15. I want to convert these to the day number of the year. In a non-leap year, Mar-15 would be 74 (the 74th day of the year) and Dec-31 would be 365. Have not yet found a way to do this in Excel. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help: How do I convert a text date into a real date format | Excel Worksheet Functions | |||
pick up date, month and year from a date | Excel Discussion (Misc queries) | |||
convert number to date format | Excel Discussion (Misc queries) | |||
holiday dates | Excel Worksheet Functions | |||
Excel: I enter date and format for date, but shows as number | Excel Discussion (Misc queries) |