Is it possible to add dates?

[S S]

Ok will try and explain with the example below.....

in column A as header `Dates`
in row A2 `all` (meaning all dates for that month)
in row A3 15-29 (meaning from 15th to 29th of that month)
in row A4 1-10 (meaning from 1st to 10th of that month)

And so on various dates for different people , its about 50 rows long, with
a column for each month of the year.

My question......
row A2 would = 30 (31) days
row A3 would =14 days
row A4 would =9 days

could I get a formula to give me the total days at the bottom of column A?
ie 53 days

Any help would be appreciated
thanks

 

[Bob Phillips]

Why is 15-29 14, not 15? Why is 1-10 9 not 10? How do you tell whether all
is 28, 29, 30 or 31?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi Bob, and many thanks for your help in times gone past.

S S

The answer appears to be (from the data in column A, put this in new column B)


=IF(A3="all",31,IF(ISERROR(FIND("-",A3)),A3,MID(A3,FIND("-",A3)+1,99)-LEFT(A3,(FIND("-",A3)-1))+1))

Then repeat this for each column, and amend the number of days that 'all'
selects to suit the month for the related column.

This will give the required number of days for 'all', or the number of days
n-m, or the number of days.

Hope this helps.

 

[Guest]

S S

An error in my formula, in that the single figure in A2 would be a day
number, and not a number of days, changes the formula (and the correct Row 2)
to:


=IF(A2="all",31,IF(ISERROR(FIND("-",A2)),IF(A2="","",1),MID(A2,FIND("-",A2)+1,99)-LEFT(A2,(FIND("-",A2)-1))+1))

I still assume that January (31 days) is your first column.

Cheers

 

[S S]

Thanks for the formula I will try it tonight.
I didnt notice your original reply as for some reason my 1st posting still
hasnt appeared...strange.

 

[Bob Phillips]

Thanks Bryan, I think I see what he is doing now (not sure I get the why,
but what the heck).

Here is an alternative that does the summing

=SUM(IF(A2:A50="all",DAY(DATE(YEAR(TODAY()),COLUMN()+1,0)),IF(ISNUMBER(FIND(
"-",A2:A50)),RIGHT(A2:A50,LEN(A2:A50)-FIND("-",A2:A50))-LEFT(A2:A50,FIND("-"
,A2:A50)-1),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Note it might need adjustment to calculate the correct number of days in a
month.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[S S]

I gave the formula a try and cant get it to work so trying to simplify it
(for me)
I can work around the `all` part (original post) and get the answer I
require for that, but how to get .....
A2 1-14 (14 days)
A3 (blank)
A4 15-29 (14 days)
A5 7-21 (14 days)

A6 would be the total 14+0+14+14 = 42 days

To let you understand what this is about, I maintain a list of dates when
members of a forum are at their holiday homes (abroad) for example the
member for cell A2 is at his holiday home in January from 1st to the 14th
and member in cell A4 is there from 15th to the 29th. The idea being that we
can meet up with members who are out at the same time. I am trying to get
some statistics for the local economy on how many days per season we are
contributing to the local community.
If I can just get a formula to add the column (how many days) I can work
around the other pieces of information I require. I have managed to get a
formula that will add the members who are there `all` days of the month so I
will just take this figure and multiply x 7 and then I can add it on to the
total I get for the above.

My apologies for being a bit dumb with these formulas, but they are way
beyond me unless its + - or divide, some when I read them I can understand
what they are doing but not many.

regards

 

[Bob Phillips]

If you want to do without the 'all', try

=SUM(IF(ISNUMBER(FIND("-",A2:A50)),
RIGHT(A2:A50,LEN(A2:A50)-FIND("-",A2:A50))-LEFT(A2:A50,FIND("-",A2:A50)-1),0
))

remember, this is an array formula, it should be committed with
Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)