ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   COUNTING DAYS WITHIN A PERIODE (https://www.excelbanter.com/excel-worksheet-functions/178758-counting-days-within-periode.html)

Jan T.[_3_]
COUNTING DAYS WITHIN A PERIODE
 
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

T. Valko
COUNTING DAYS WITHIN A PERIODE
 
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

Jan T.[_3_]
COUNTING DAYS WITHIN A PERIODE
 
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

T. Valko
COUNTING DAYS WITHIN A PERIODE
 
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

Jan T.[_3_]
COUNTING DAYS WITHIN A PERIODE
 
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

T. Valko
COUNTING DAYS WITHIN A PERIODE
 
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

Pete_UK
COUNTING DAYS WITHIN A PERIODE
 
Biff,

I think Jan is asking if you can convert your MIN and MAX formula so
that it only uses IFs - something like:

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

to this:

=IF((IF(D2B2,B2,D2)-IF(A2C2,A2,C2)+1)<0,0,IF(D2B2,B2,D2)-
IF(A2C2,A2,C2)+1)

I think this does it, but you might want to check !! <g

Pete

On Mar 4, 11:27*pm, "T. Valko" wrote:
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,StartDat*e,FROM))

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

Ron Coderre
COUNTING DAYS WITHIN A PERIODE
 
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

Ron Coderre
COUNTING DAYS WITHIN A PERIODE
 
I revisited what you're really trying to do
and came up with this MS Access formula:

New:
nz(Switch([EndDate]<[from],0,[StartDate][Until],0),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?
--------------------------

Regards,

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

"Ron Coderre" wrote in message
...
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

Jan T.[_3_]
COUNTING DAYS WITHIN A PERIODE
 
Wow, that was something. I now have excactly what I was looking for both in
Excel, but
also in Access (2000). Helps me a lot, both to understand Excel worksheets
functions better
and SQL at the same time. I will sure use the formulas in both applications.

So thanx a million for helping. I am sure this will also help many others as
well.

Jan T.



"Ron Coderre" skrev i melding
...
I revisited what you're really trying to do
and came up with this MS Access formula:

New:
nz(Switch([EndDate]<[from],0,[StartDate][Until],0),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?
--------------------------

Regards,

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

"Ron Coderre" wrote in message
...
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

Ron Coderre[_2_]
COUNTING DAYS WITHIN A PERIODE
 
You're very welcome, Jan...I'm glad I could help.

--------------------------

Regards,

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

"Jan T." wrote in message
...
Wow, that was something. I now have excactly what I was looking for both
in Excel, but
also in Access (2000). Helps me a lot, both to understand Excel worksheets
functions better
and SQL at the same time. I will sure use the formulas in both
applications.

So thanx a million for helping. I am sure this will also help many others
as well.

Jan T.



"Ron Coderre" skrev i melding
...
I revisited what you're really trying to do
and came up with this MS Access formula:

New:
nz(Switch([EndDate]<[from],0,[StartDate][Until],0),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?
--------------------------

Regards,

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

"Ron Coderre" wrote in message
...
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


All times are GMT +1. The time now is 05:42 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com