Decimal Days

[tsumanii]

Hello, I have a very large data set(>7000 records) where the date i
split over several columns. There are columns for years
months(numerical),days,hours, minutes. I need to convert this t
'decimal days' in order to study the data, but don't know how to d
this without going through the data and correcting each data item.
need to take into account leap years and each month having differen
numbers of days. Is there a way to get excel to recognise that mont
'1' has 31 days for example and that in certain years month 2 has 2
days?!

I thought maybe i could use a macro or something but i dont know how t
use them - help!!!!

hope someone can point me in the right direction!!!
cheers
sue

 

[Bob Phillips]

Could you create a column to get the full date, something like

=DATE(A1,B1.C1)

and format the result cell as General. You will then get the number of days
since Jan 1 1900.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

If the dates are later than January 0 1900 then excel will know if a month
has 28 or 29 days
Assume the years starts in A2, months in B2 and days in C2

=DATE(A2,B2,C2)

will return a date where one day = 1

if for instance there is an error so that that it can look like

1965 2 31

then the formula will return

03/03/1965

if you would need a date like that to return the last date of february you
can amend the formula

=IF(MONTH(DATE(A2,B2,C2))<>B2,DATE(A2,B2+1,0),DATE(A2,B2,C2))

 

[tsumanii]

THANKYOU!!!!!!! i now have decimal days