Exel convert a date to number: 11/10 to 314

November 14, 2010 at 13:19:37
Specs: Windows XP, 2.6 GHz, 1 Gb RAM
Working in Open Office Calc 3.0, I need to flag dates, in order to flag they need to be numbers,
how do I convert the dd/mm format to the actual number of the day in the year?
EX: 1/1 =1, 12/31 =365.
Issue is:
Input data as dd/mm, display number of day in year.

Any help would be greatly appreciated. I need the format set in the cell, if possible.

See More: Exel convert a date to number: 11/10 to 314

Report •

November 14, 2010 at 13:24:37
I know there is a better way to this... but you could make a table somewhere that has the date in one column and numbers 1 to 365 in an adjacent column, and then do a vlookup

Report •

November 14, 2010 at 13:27:14
nevermind... I just re-read your post. You are looking for a cell format that will automatically show the day of the year when you input a date. I'm not sure if that's possible or not. If it were me, I would just let the user input the date and then convert it. =A1-DATE(YEAR(A1),1,0)

Report •

November 15, 2010 at 06:58:10
Just for fun, here's another solution using the little known YEARFRAC function...


Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

Related Solutions

November 15, 2010 at 07:45:23
There also a =DAYSINYEAR() function:


I don't use Calc but something like:

DAYSINYEAR("2010"&A1) might work also.




Report •

Ask Question