I am trying to count days in a period i.e. a month + having a start date
and an end date to calculate from.

I have too columns for log dates. These a

StartDate EndDate

Now I want to calculate how many days is there within a given month.

To acheive this, I figure I had to add some moore columns and add
some data to them. The columns From and Until is the period which
a want to measure. In this case that would be the month February.
How many days in February is there (NumOfDays) if StartDate and
EndDate are as the following example below?

StartDate EndDate | From Until
NumOfDays.
--------------------------------------------------------------------------
02/01/08 02/03/08 | 02/01/08 02/29/08 3
01/25/08 02/03/08 | 02/01/08 02/29/08 3
02/03/08 02/03/08 | 02/01/08 02/29/08 1
02/29/08 03/01/08 | 02/01/08 02/29/08 1
01/29/08 03/03/08 | 02/01/08 02/29/08 29
03/02/08 03/03/08 | 02/01/08 02/29/08 0

What would the formula in the very right Column look like (i.e. NumOfDays
Column)
If I want the results as you can see them above?

I tried something like this:

=(MIN(Until,MAX(StartDate, FromDate) +1) - MIN(Until, MAX(EndDate, FromDate)

This works fine for almost all cases except for the last wich should
returned 0 and
not 1 as my formula gives me. Any suggestions?

Thank you very much for your help.

Regards
Jan

Try this:

=MAX(0,MIN(B2,D2)-MAX(A2,C2)+1)

Whe

B2 = end date
D2 = until
A2 = start date
C2 = from


--
Biff
Microsoft Excel MVP


"Jan T." wrote in message
...
I am trying to count days in a period i.e. a month + having a start date
and an end date to calculate from.

I have too columns for log dates. These a

StartDate EndDate

Now I want to calculate how many days is there within a given month.

To acheive this, I figure I had to add some moore columns and add
some data to them. The columns From and Until is the period which
a want to measure. In this case that would be the month February.
How many days in February is there (NumOfDays) if StartDate and
EndDate are as the following example below?

StartDate EndDate | From Until NumOfDays.
--------------------------------------------------------------------------
02/01/08 02/03/08 | 02/01/08 02/29/08 3
01/25/08 02/03/08 | 02/01/08 02/29/08 3
02/03/08 02/03/08 | 02/01/08 02/29/08 1
02/29/08 03/01/08 | 02/01/08 02/29/08 1
01/29/08 03/03/08 | 02/01/08 02/29/08 29
03/02/08 03/03/08 | 02/01/08 02/29/08 0

What would the formula in the very right Column look like (i.e. NumOfDays
Column)
If I want the results as you can see them above?

I tried something like this:

=(MIN(Until,MAX(StartDate, FromDate) +1) - MIN(Until, MAX(EndDate,
FromDate)

This works fine for almost all cases except for the last wich should
returned 0 and
not 1 as my formula gives me. Any suggestions?

Thank you very much for your help.

Regards
Jan

Thanks! That works like a dream! I will certainly use this function many
times.

One other question though. I now need to put the same calculation into a
query
in Access. Then I cannot use MIN and MAX function but are limited
to use for example IIF(A<B,A,B) and so on. Is it possible to do this
calculation without using MIN and MAX functions and have the same results?

It seems very complicated to me when I gave it a try.

Any suggestions?

I am very thankful for any help to solve this formula.


Jan






"T. Valko" skrev i melding
...
Try this:

=MAX(0,MIN(B2,D2)-MAX(A2,C2)+1)

Whe

B2 = end date
D2 = until
A2 = start date
C2 = from


--
Biff
Microsoft Excel MVP


"Jan T." wrote in message
...
I am trying to count days in a period i.e. a month + having a start date
and an end date to calculate from.

I have too columns for log dates. These a

StartDate EndDate

Now I want to calculate how many days is there within a given month.

To acheive this, I figure I had to add some moore columns and add
some data to them. The columns From and Until is the period which
a want to measure. In this case that would be the month February.
How many days in February is there (NumOfDays) if StartDate and
EndDate are as the following example below?

StartDate EndDate | From Until NumOfDays.
--------------------------------------------------------------------------
02/01/08 02/03/08 | 02/01/08 02/29/08 3
01/25/08 02/03/08 | 02/01/08 02/29/08 3
02/03/08 02/03/08 | 02/01/08 02/29/08 1
02/29/08 03/01/08 | 02/01/08 02/29/08 1
01/29/08 03/03/08 | 02/01/08 02/29/08 29
03/02/08 03/03/08 | 02/01/08 02/29/08 0

What would the formula in the very right Column look like (i.e. NumOfDays
Column)
If I want the results as you can see them above?

I tried something like this:

=(MIN(Until,MAX(StartDate, FromDate) +1) - MIN(Until, MAX(EndDate,
FromDate)

This works fine for almost all cases except for the last wich should
returned 0 and
not 1 as my formula gives me. Any suggestions?

Thank you very much for your help.

Regards
Jan

Is it possible to do this calculation without using MIN and MAX functions
and have the same results?

Not nearly as elegant:

=SUMPRODUCT(--(ROW(INDIRECT(A2&":"&B2))=C2),--(ROW(INDIRECT(A2&":"&B2))<=D2))


--
Biff
Microsoft Excel MVP


"Jan T." wrote in message
...
Thanks! That works like a dream! I will certainly use this function many
times.

One other question though. I now need to put the same calculation into a
query
in Access. Then I cannot use MIN and MAX function but are limited
to use for example IIF(A<B,A,B) and so on. Is it possible to do this
calculation without using MIN and MAX functions and have the same results?

It seems very complicated to me when I gave it a try.

Any suggestions?

I am very thankful for any help to solve this formula.


Jan






"T. Valko" skrev i melding
...
Try this:

=MAX(0,MIN(B2,D2)-MAX(A2,C2)+1)

Whe

B2 = end date
D2 = until
A2 = start date
C2 = from


--
Biff
Microsoft Excel MVP


"Jan T." wrote in message
...
I am trying to count days in a period i.e. a month + having a start date
and an end date to calculate from.

I have too columns for log dates. These a

StartDate EndDate

Now I want to calculate how many days is there within a given month.

To acheive this, I figure I had to add some moore columns and add
some data to them. The columns From and Until is the period which
a want to measure. In this case that would be the month February.
How many days in February is there (NumOfDays) if StartDate and
EndDate are as the following example below?

StartDate EndDate | From Until NumOfDays.
--------------------------------------------------------------------------
02/01/08 02/03/08 | 02/01/08 02/29/08 3
01/25/08 02/03/08 | 02/01/08 02/29/08 3
02/03/08 02/03/08 | 02/01/08 02/29/08 1
02/29/08 03/01/08 | 02/01/08 02/29/08 1
01/29/08 03/03/08 | 02/01/08 02/29/08 29
03/02/08 03/03/08 | 02/01/08 02/29/08 0

What would the formula in the very right Column look like (i.e.
NumOfDays Column)
If I want the results as you can see them above?

I tried something like this:

=(MIN(Until,MAX(StartDate, FromDate) +1) - MIN(Until, MAX(EndDate,
FromDate)

This works fine for almost all cases except for the last wich should
returned 0 and
not 1 as my formula gives me. Any suggestions?

Thank you very much for your help.

Regards
Jan

Oh, I am sorry if I was not clear enough. I need a formula that can be used
in ACCESS QBE or Query.

I don't think this formula will work in a query, right?
(However, I am very impressed of what you put together that will work in
Excel.. :) )

This is the formula I started out with in Access Query:
RESULT:
(IIf([UNTIL]<[EndDate],[UNTIL],IIf([EndDate][FROM],[EndDate],[FROM]))+1)-
(IIf([UNTIL]<[StartDate],[UNTIL],IIf([StartDate][FROM],[StartDate],[FROM])))

or the same formula in EXCEL;

=IF(UNTIL<IF(EndDateFROM,EndDate,FROM),UNTIL,IF(E ndDateFROM,EndDate,FROM))-
IF(UNTIL<IF(StartDateFROM,StartDate,FROM),UNTIL,I F(StartDateFROM,StartDate,FROM))

It gives me all the answers except it also gives me 1 where I excpected 0.

(You do not have to write SQL-kode). An Excel formula using if( stmnt, 1, 2)
and so on will be just
fine. Or should I say Super!

Thank you so much so far. I really apreciate your help!

Regards
Jan



"T. Valko" skrev i melding
...
Is it possible to do this calculation without using MIN and MAX functions
and have the same results?

Not nearly as elegant:

=SUMPRODUCT(--(ROW(INDIRECT(A2&":"&B2))=C2),--(ROW(INDIRECT(A2&":"&B2))<=D2))


--
Biff
Microsoft Excel MVP


"Jan T." wrote in message
...
Thanks! That works like a dream! I will certainly use this function many
times.

One other question though. I now need to put the same calculation into a
query
in Access. Then I cannot use MIN and MAX function but are limited
to use for example IIF(A<B,A,B) and so on. Is it possible to do this
calculation without using MIN and MAX functions and have the same
results?

It seems very complicated to me when I gave it a try.

Any suggestions?

I am very thankful for any help to solve this formula.


Jan






"T. Valko" skrev i melding
...
Try this:

=MAX(0,MIN(B2,D2)-MAX(A2,C2)+1)

Whe

B2 = end date
D2 = until
A2 = start date
C2 = from


--
Biff
Microsoft Excel MVP


"Jan T." wrote in message
...
I am trying to count days in a period i.e. a month + having a start date
and an end date to calculate from.

I have too columns for log dates. These a

StartDate EndDate

Now I want to calculate how many days is there within a given month.

To acheive this, I figure I had to add some moore columns and add
some data to them. The columns From and Until is the period which
a want to measure. In this case that would be the month February.
How many days in February is there (NumOfDays) if StartDate and
EndDate are as the following example below?

StartDate EndDate | From Until NumOfDays.
--------------------------------------------------------------------------
02/01/08 02/03/08 | 02/01/08 02/29/08 3
01/25/08 02/03/08 | 02/01/08 02/29/08 3
02/03/08 02/03/08 | 02/01/08 02/29/08 1
02/29/08 03/01/08 | 02/01/08 02/29/08 1
01/29/08 03/03/08 | 02/01/08 02/29/08 29
03/02/08 03/03/08 | 02/01/08 02/29/08 0

What would the formula in the very right Column look like (i.e.
NumOfDays Column)
If I want the results as you can see them above?

I tried something like this:

=(MIN(Until,MAX(StartDate, FromDate) +1) - MIN(Until, MAX(EndDate,
FromDate)

This works fine for almost all cases except for the last wich should
returned 0 and
not 1 as my formula gives me. Any suggestions?

Thank you very much for your help.

Regards
Jan

You might have better luck if you posted in an Access newsgroup.


--
Biff
Microsoft Excel MVP


"Jan T." wrote in message
...
Oh, I am sorry if I was not clear enough. I need a formula that can be
used in ACCESS QBE or Query.

I don't think this formula will work in a query, right?
(However, I am very impressed of what you put together that will work in
Excel.. :) )

This is the formula I started out with in Access Query:
RESULT:
(IIf([UNTIL]<[EndDate],[UNTIL],IIf([EndDate][FROM],[EndDate],[FROM]))+1)-
(IIf([UNTIL]<[StartDate],[UNTIL],IIf([StartDate][FROM],[StartDate],[FROM])))

or the same formula in EXCEL;

=IF(UNTIL<IF(EndDateFROM,EndDate,FROM),UNTIL,IF(E ndDateFROM,EndDate,FROM))-
IF(UNTIL<IF(StartDateFROM,StartDate,FROM),UNTIL,I F(StartDateFROM,StartDate,FROM))

It gives me all the answers except it also gives me 1 where I excpected 0.

(You do not have to write SQL-kode). An Excel formula using if( stmnt, 1,
2) and so on will be just
fine. Or should I say Super!

Thank you so much so far. I really apreciate your help!

Regards
Jan



"T. Valko" skrev i melding
...
Is it possible to do this calculation without using MIN and MAX
functions and have the same results?

Not nearly as elegant:

=SUMPRODUCT(--(ROW(INDIRECT(A2&":"&B2))=C2),--(ROW(INDIRECT(A2&":"&B2))<=D2))


--
Biff
Microsoft Excel MVP


"Jan T." wrote in message
...
Thanks! That works like a dream! I will certainly use this function many
times.

One other question though. I now need to put the same calculation into a
query
in Access. Then I cannot use MIN and MAX function but are limited
to use for example IIF(A<B,A,B) and so on. Is it possible to do this
calculation without using MIN and MAX functions and have the same
results?

It seems very complicated to me when I gave it a try.

Any suggestions?

I am very thankful for any help to solve this formula.


Jan






"T. Valko" skrev i melding
...
Try this:

=MAX(0,MIN(B2,D2)-MAX(A2,C2)+1)

Whe

B2 = end date
D2 = until
A2 = start date
C2 = from


--
Biff
Microsoft Excel MVP


"Jan T." wrote in message
...
I am trying to count days in a period i.e. a month + having a start
date
and an end date to calculate from.

I have too columns for log dates. These a

StartDate EndDate

Now I want to calculate how many days is there within a given month.

To acheive this, I figure I had to add some moore columns and add
some data to them. The columns From and Until is the period which
a want to measure. In this case that would be the month February.
How many days in February is there (NumOfDays) if StartDate and
EndDate are as the following example below?

StartDate EndDate | From Until NumOfDays.
--------------------------------------------------------------------------
02/01/08 02/03/08 | 02/01/08 02/29/08 3
01/25/08 02/03/08 | 02/01/08 02/29/08 3
02/03/08 02/03/08 | 02/01/08 02/29/08 1
02/29/08 03/01/08 | 02/01/08 02/29/08 1
01/29/08 03/03/08 | 02/01/08 02/29/08 29
03/02/08 03/03/08 | 02/01/08 02/29/08 0

What would the formula in the very right Column look like (i.e.
NumOfDays Column)
If I want the results as you can see them above?

I tried something like this:

=(MIN(Until,MAX(StartDate, FromDate) +1) - MIN(Until, MAX(EndDate,
FromDate)

This works fine for almost all cases except for the last wich should
returned 0 and
not 1 as my formula gives me. Any suggestions?

Thank you very much for your help.

Regards
Jan

Hi, Jan

Since you need the formula in MS Access,
tr this (in sections for readability

RESULT: (nz(Switch([UNTIL]<[EndDate],[UNTIL],[EndDate][FROM],
[EndDate]),[FROM])+1)-nz(Switch([UNTIL]<[StartDate],[UNTIL],
[StartDate][FROM],[StartDate]),[FROM])

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Jan T." wrote in message
...
Oh, I am sorry if I was not clear enough. I need a formula that can be
used in ACCESS QBE or Query.

I don't think this formula will work in a query, right?
(However, I am very impressed of what you put together that will work in
Excel.. :) )

This is the formula I started out with in Access Query:
RESULT:
(IIf([UNTIL]<[EndDate],[UNTIL],IIf([EndDate][FROM],[EndDate],[FROM]))+1)-
(IIf([UNTIL]<[StartDate],[UNTIL],IIf([StartDate][FROM],[StartDate],[FROM])))

or the same formula in EXCEL;

=IF(UNTIL<IF(EndDateFROM,EndDate,FROM),UNTIL,IF(E ndDateFROM,EndDate,FROM))-
IF(UNTIL<IF(StartDateFROM,StartDate,FROM),UNTIL,I F(StartDateFROM,StartDate,FROM))

It gives me all the answers except it also gives me 1 where I excpected 0.

(You do not have to write SQL-kode). An Excel formula using if( stmnt, 1,
2) and so on will be just
fine. Or should I say Super!

Thank you so much so far. I really apreciate your help!

Regards
Jan



"T. Valko" skrev i melding
...
Is it possible to do this calculation without using MIN and MAX
functions and have the same results?

Not nearly as elegant:

=SUMPRODUCT(--(ROW(INDIRECT(A2&":"&B2))=C2),--(ROW(INDIRECT(A2&":"&B2))<=D2))


--
Biff
Microsoft Excel MVP


"Jan T." wrote in message
...
Thanks! That works like a dream! I will certainly use this function many
times.

One other question though. I now need to put the same calculation into a
query
in Access. Then I cannot use MIN and MAX function but are limited
to use for example IIF(A<B,A,B) and so on. Is it possible to do this
calculation without using MIN and MAX functions and have the same
results?

It seems very complicated to me when I gave it a try.

Any suggestions?

I am very thankful for any help to solve this formula.


Jan






"T. Valko" skrev i melding
...
Try this:

=MAX(0,MIN(B2,D2)-MAX(A2,C2)+1)

Whe

B2 = end date
D2 = until
A2 = start date
C2 = from


--
Biff
Microsoft Excel MVP


"Jan T." wrote in message
...
I am trying to count days in a period i.e. a month + having a start
date
and an end date to calculate from.

I have too columns for log dates. These a

StartDate EndDate

Now I want to calculate how many days is there within a given month.

To acheive this, I figure I had to add some moore columns and add
some data to them. The columns From and Until is the period which
a want to measure. In this case that would be the month February.
How many days in February is there (NumOfDays) if StartDate and
EndDate are as the following example below?

StartDate EndDate | From Until NumOfDays.
--------------------------------------------------------------------------
02/01/08 02/03/08 | 02/01/08 02/29/08 3
01/25/08 02/03/08 | 02/01/08 02/29/08 3
02/03/08 02/03/08 | 02/01/08 02/29/08 1
02/29/08 03/01/08 | 02/01/08 02/29/08 1
01/29/08 03/03/08 | 02/01/08 02/29/08 29
03/02/08 03/03/08 | 02/01/08 02/29/08 0

What would the formula in the very right Column look like (i.e.
NumOfDays Column)
If I want the results as you can see them above?

I tried something like this:

=(MIN(Until,MAX(StartDate, FromDate) +1) - MIN(Until, MAX(EndDate,
FromDate)

This works fine for almost all cases except for the last wich should
returned 0 and
not 1 as my formula gives me. Any suggestions?

Thank you very much for your help.

Regards
Jan