COUNTING DAYS WITHIN A PERIODE

J

Jan T.

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 are:

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

T. Valko

Try this:

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

Where:

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

Jan T.

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

T. Valko

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

Not nearly as elegant:

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

Jan T.

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(EndDate>FROM,EndDate,FROM),UNTIL,IF(EndDate>FROM,EndDate,FROM))-
IF(UNTIL<IF(StartDate>FROM,StartDate,FROM),UNTIL,IF(StartDate>FROM,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

T. Valko

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


--
Biff
Microsoft Excel MVP


Jan T. said:
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(EndDate>FROM,EndDate,FROM),UNTIL,IF(EndDate>FROM,EndDate,FROM))-
IF(UNTIL<IF(StartDate>FROM,StartDate,FROM),UNTIL,IF(StartDate>FROM,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
Click to expand...
 
P

Pete_UK

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(D2>B2,B2,D2)-IF(A2>C2,A2,C2)+1)<0,0,IF(D2>B2,B2,D2)-
IF(A2>C2,A2,C2)+1)

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

Pete

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

--
Biff
Microsoft Excel MVP




Oh, I am sorry if I was not clear enough. I need a formula that can be
used in ACCESS QBE or Query.
Click to expand...
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.. :)  )
Click to expand...
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])­))
Click to expand...
       or the same formula in EXCEL;

It gives me all the answers except it also gives me 1 where I excpected 0.
Click to expand...
(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!
Click to expand...
Thank you so much so far. I really apreciate your help!
Click to expand...

- Show quoted text -
Click to expand...
 
R

Ron Coderre

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. said:
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(EndDate>FROM,EndDate,FROM),UNTIL,IF(EndDate>FROM,EndDate,FROM))-
IF(UNTIL<IF(StartDate>FROM,StartDate,FROM),UNTIL,IF(StartDate>FROM,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
Click to expand...
 
R

Ron Coderre

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 said:
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. said:
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(EndDate>FROM,EndDate,FROM),UNTIL,IF(EndDate>FROM,EndDate,FROM))-
IF(UNTIL<IF(StartDate>FROM,StartDate,FROM),UNTIL,IF(StartDate>FROM,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 said:
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


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" <[email protected]> skrev i melding
Try this:

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

Where:

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


--
Biff
Microsoft Excel MVP


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 are:

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
Click to expand...
Click to expand...
Click to expand...
 
J

Jan T.

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 said:
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 said:
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. said:
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(EndDate>FROM,EndDate,FROM),UNTIL,IF(EndDate>FROM,EndDate,FROM))-
IF(UNTIL<IF(StartDate>FROM,StartDate,FROM),UNTIL,IF(StartDate>FROM,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" <[email protected]> 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


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" <[email protected]> skrev i melding
Try this:

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

Where:

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


--
Biff
Microsoft Excel MVP


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 are:

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
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Coderre

You're very welcome, Jan...I'm glad I could help.

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

Regards,

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

Jan T. said:
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 said:
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 said:
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)

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(EndDate>FROM,EndDate,FROM),UNTIL,IF(EndDate>FROM,EndDate,FROM))-
IF(UNTIL<IF(StartDate>FROM,StartDate,FROM),UNTIL,IF(StartDate>FROM,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" <[email protected]> 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


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" <[email protected]> skrev i melding
Try this:

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

Where:

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


--
Biff
Microsoft Excel MVP


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 are:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Rate of change 4
Date Function 2
Find then highlight in yellow 6
Reverse Index Match 2
Modifying a complex date formual to count months 4
When building a chart Dates are been included that are not in thedate range 2
max date based on criteria 2

Top