Hi there, I'm not quite sure if this can be done or not, but any help/advice
would be really appreciated.

I would like to work out capacity (in days) of a certain month from
different start dates and end dates. As an example below, I would like to
display for each record how many days in January are being used; so row 2
should display 26 days in January, row 3 the full 31 days in January, and row
4 should be 2 days in January.

A B
1 start end
2 14th Nov 08 26th Jan 09
3 16th Oct 08 04 Feb 09
4 30th Jan 09 10 Feb 09

Is there a formula to display this?
thanks,
Danny.

Hi Danny,

=MIN(B2,DATE(2009,1,31))-MAX(A2,DATE(2009,1,1))+1

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Danny" wrote in message
...
Hi there, I'm not quite sure if this can be done or not, but any
help/advice
would be really appreciated.

I would like to work out capacity (in days) of a certain month from
different start dates and end dates. As an example below, I would like to
display for each record how many days in January are being used; so row 2
should display 26 days in January, row 3 the full 31 days in January, and
row
4 should be 2 days in January.

A B
1 start end
2 14th Nov 08 26th Jan 09
3 16th Oct 08 04 Feb 09
4 30th Jan 09 10 Feb 09

Is there a formula to display this?
thanks,
Danny.

That's fantastic, thank you Nick!

"Niek Otten" wrote:

Hi Danny,

=MIN(B2,DATE(2009,1,31))-MAX(A2,DATE(2009,1,1))+1

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Danny" wrote in message
...
Hi there, I'm not quite sure if this can be done or not, but any
help/advice
would be really appreciated.

I would like to work out capacity (in days) of a certain month from
different start dates and end dates. As an example below, I would like to
display for each record how many days in January are being used; so row 2
should display 26 days in January, row 3 the full 31 days in January, and
row
4 should be 2 days in January.

A B
1 start end
2 14th Nov 08 26th Jan 09
3 16th Oct 08 04 Feb 09
4 30th Jan 09 10 Feb 09

Is there a formula to display this?
thanks,
Danny.

--Suppose you have your data arranged in the below format. Please note that
the months displayed in cell C1 is entered in excel date formats
formatted to display as mmm-yy

Col A Col B Col C
Date In Date Out Jan-09
9-Jan-09 16-Mar-09 23

--The formula to be applied in cell C2 is given below. Copy the cells to the
right as required

=MAX(0,MIN($B2,DATE(YEAR(C$1),MONTH(C$1)+1,1))-MAX($A2+1,DATE(YEAR(C$1),MONTH(C$1),1)))

--
If this post helps click Yes
---------------
Jacob Skaria


"Danny" wrote:

Hi there, I'm not quite sure if this can be done or not, but any help/advice
would be really appreciated.

I would like to work out capacity (in days) of a certain month from
different start dates and end dates. As an example below, I would like to
display for each record how many days in January are being used; so row 2
should display 26 days in January, row 3 the full 31 days in January, and row
4 should be 2 days in January.

A B
1 start end
2 14th Nov 08 26th Jan 09
3 16th Oct 08 04 Feb 09
4 30th Jan 09 10 Feb 09

Is there a formula to display this?
thanks,
Danny.

Try this

=IF(A2<DATE(2009,1,1),IF(B2DATE(2009,1,31),31,B2-DATE
(2009,1,1)+1),DATE(2009,1,31)+1-A2)



On Aug 13, 3:06*pm, Danny wrote:
Hi there, I'm not quite sure if this can be done or not, but any help/advice
would be really appreciated.

I would like to work out capacity (in days) of a certain month from
different start dates and end dates. As an example below, I would like to
display for each record how many days in January are being used; so row 2
should display 26 days in January, row 3 the full 31 days in January, and row
4 should be 2 days in January.

* * * * * A * * * * * * * * * * B
1 * start * * * * * * * * end
2 * 14th Nov 08 * * * 26th Jan 09
3 * 16th Oct 08 * * * 04 Feb 09
4 * 30th Jan 09 * * * 10 Feb 09

Is there a formula to display this?
thanks,
Danny.

What if I wanted the workin days? ie not including sat & Sun ?

Andrew

"muddan madhu" wrote:

Try this

=IF(A2<DATE(2009,1,1),IF(B2DATE(2009,1,31),31,B2-DATE
(2009,1,1)+1),DATE(2009,1,31)+1-A2)



On Aug 13, 3:06 pm, Danny wrote:
Hi there, I'm not quite sure if this can be done or not, but any help/advice
would be really appreciated.

I would like to work out capacity (in days) of a certain month from
different start dates and end dates. As an example below, I would like to
display for each record how many days in January are being used; so row 2
should display 26 days in January, row 3 the full 31 days in January, and row
4 should be 2 days in January.

A B
1 start end
2 14th Nov 08 26th Jan 09
3 16th Oct 08 04 Feb 09
4 30th Jan 09 10 Feb 09

Is there a formula to display this?
thanks,
Danny.

Look at the NETWORKDAYS function (Excel help tells you how it works).
--
David Biddulph

"Andrew" wrote in message
...
What if I wanted the workin days? ie not including sat & Sun ?

Andrew

"muddan madhu" wrote:

Try this

=IF(A2<DATE(2009,1,1),IF(B2DATE(2009,1,31),31,B2-DATE
(2009,1,1)+1),DATE(2009,1,31)+1-A2)



On Aug 13, 3:06 pm, Danny wrote:
Hi there, I'm not quite sure if this can be done or not, but any
help/advice
would be really appreciated.

I would like to work out capacity (in days) of a certain month from
different start dates and end dates. As an example below, I would like
to
display for each record how many days in January are being used; so row
2
should display 26 days in January, row 3 the full 31 days in January,
and row
4 should be 2 days in January.

A B
1 start end
2 14th Nov 08 26th Jan 09
3 16th Oct 08 04 Feb 09
4 30th Jan 09 10 Feb 09

Is there a formula to display this?
thanks,
Danny.