Date formula

  • Thread starter Thread starter Max_power
  • Start date Start date
M

Max_power

Hello,

I'm trying to create a formula that will allow me to check the
intervals in between dates. For instance I have
A1:1000 = Jan
B1:1000 = Feb
.....
L1:1000 = Dec

Contained in *most*of these cells I have data

Is it possible for me to count the number of days/months in between
each period. Say I have data in Dec, Nov, Oct but not in Sep, Aug, but
again in July, June, etc.....

Is this to be done with a countif statement?
I have the following but it doesn't return what I'm looking for
=COUNTIF(O5,\">=\"&J5-30)-COUNTIF(O5,\">\"&J5)

Many many thanks
 
Sorry but I don't get it...
If 12 columns correspond to months, then where are the days;
surely not the rows since there are 1000! Are you saying that in each
column you can have 1000 date entries; for example:

*Say column 2, row 5 has: 02/03/06 and column 4, row 5 has 04/16/06.
Are you asking for the days spanned formatted as the number of months
and remainder (i.e., days).
*Do you have an actual example of your data available???
 
Thanks for the response.

Lets say I have months jan:Dec which are A:L respectively
I'll have data in most of the rows from A to L

I just want to have formula whereby I pick say the last month and fin
when the last occurance of data was entered ie what month.

So if I pick December as the starting point, if nothing was entered i
the cell corresponding to Nov, or Oct but it does in September it wil
return 3 ie 3 months have passed since data has been entered.

Many thanks for any help on the matter

Ma
 
Hi Max

One way
Insert 2 rows above your data, so that Jan to Dec is in row 3.
In cell A2 enter
=COUNTA(A4:A1004)
In cell A1 enter
=COLUMN()*(A2>0)
copy A1:A2 through B1:L1
Now, if there is any data at all in the columns, the column number will
appear in row 1, otherwise it will be zero.
To find the number of months prior to December that data has been
entered, then
In cell M1 enter
=12-Max(A1:K1)

--
Regards

Roger Govier


"Max_power" <[email protected]>
wrote in message
news:[email protected]...
 
Back
Top