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 •

#1
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 •

#2
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 •

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

=ROUNDUP(YEARFRAC(DATEVALUE("1/1/"&YEAR(A1)),A1,1)*365,0)+1

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


Report •

Related Solutions

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

DAYSINYEAR("date")

I don't use Calc but something like:

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

http://wiki.services.openoffice.org...

MIKE

http://www.skeptic.com/


Report •

Ask Question