8 Replies Latest reply on Mar 29, 2016 11:50 PM by keerthi ogirala

# Guide on how to calculate month wise no of days between two date parameters ?

Hi ,

Can anyone please guide how to calculate month wise no of days between start and end date parameters ?

I calculate no of days between two date parameters .

but its not dividing according to month .  Can anyone please suggest me ?

Thanks.

• ###### 1. Re: Guide on how to calculate month wise no of days between two date parameters ?
```DATEDIFF('day', MIN([DATE]), MAX([DATE])) + 1 ?
```
• ###### 2. Re: Guide on how to calculate month wise no of days between two date parameters ?

or - if your data is sparse - this will be better:

```DATEDIFF('day', DATETRUNC('month', MIN([Date])), DATETRUNC('month', DATEADD('month', 1, MIN([Date]))))
```
• ###### 3. Re: Guide on how to calculate month wise no of days between two date parameters ?

Howdy Keerthi,

Are you trying to use those two parameters and set them as your limits? Then you want to generate a list of months and see how many days that are inbetween the two parameters fall into each month?  If so we are on the wrong track .What we've done is created a formula based completely on parameters. Parameters are global. This is why we are returning the same value multiple times. There is no representation of your Month(Date) in the calculation which I assume is just asking for the days difference between the two parameter dates.

The logic here would be we want to count all days in each month that are also between the parameters.

Cheers

Carl Slifer

InterWorks

• ###### 4. Re: Guide on how to calculate month wise no of days between two date parameters ?

Yes Carl , According to selection of two date parameters ,  need to show months and no of days in each month.

• ###### 5. Re: Guide on how to calculate month wise no of days between two date parameters ?

Hi Keerthi,

Pls check the workbook.

Mahfooj

• ###### 6. Re: Guide on how to calculate month wise no of days between two date parameters ?

Hi Mahfooj,

But my requirement is ,need to count JAN no of days 31,FEB no of days 28....etc.

Please find the below screenshot :

Thanks.

Keerthi

• ###### 7. Re: Guide on how to calculate month wise no of days between two date parameters ?

If this is not what you need then I do not know....

Now the calculation is as follows:

```DATEDIFF('day',
IF [Start Date]>=MIN([Order Date]) THEN [Start Date] ELSE DATETRUNC('month', MIN([Order Date])) END,
IF [End Date]<= MAX([Order Date]) THEN DATEADD('day', 1, [End Date]) ELSE DATETRUNC('month', DATEADD('month', 1, MIN([Order Date]))) END)
```

it is v9.2

• ###### 8. Re: Guide on how to calculate month wise no of days between two date parameters ?

Thanks a lot