8 Replies Latest reply on Jun 27, 2016 1:23 PM by Saurabh Siroya

# Spread value between start date and end date

Hi,

I would like to spread the "days spent fishing" value between the "trip start date" and the "trip end date" to show the average days spent fishing for that trip.

Trip nameTrip Start dateTrip End DateDays spent fishingAverage days spent fishing
Belize23/10/1228/11/12260.722222222
Mexico25/10/1217/12/12270.509433962
Uruguay14/12/1213/01/13120.4
**formula**

=D4/(C4-B4)

Any ideas?

• ###### 1. Re: Spread value between start date and end date

Hi Lucas,

average days spent fishing for that trip.

Can you tell me according to you what should be "average days spent fishing" for all the trips? Can I get values as per your calculation? It will be great if you describe the process to calculate the same.

As per what has described above, For Trip "Belize", do you want me to calculate the average as 3/5 where 5 is the date difference between start date and End date.

Let me know more on this. I will be glad to help you further.

-Ashish

• ###### 2. Re: Spread value between start date and end date

Hi Ashish,

Thanks for the quick response.

I have modified the original post as I was having some issues with the table, however it works fine now.

I have also added a calculation for working out the average number of days spent per trip.

What I would like to end up with is a total average number of days spent fishing for per day during the period, calculated from the average.

E.g. 26/10/2012 would display 1.231656184 (0.722222222+0.509433962), 27/10/2012 would also display 1.231656184, 29/11/2012 would display as 0.509433962. It is essential that the breakdown per trip is still available too.

• ###### 3. Re: Spread value between start date and end date

example dates are not matching with the data that you have shared. Above formula that you have mentioned is showing subtraction and while giving an example you are adding things. can you provide some clarity on that?

• ###### 4. Re: Spread value between start date and end date

In what way do the dates not match with the data?

The crucial thing is that not every day of each trip is spent fishing.

• ###### 5. Re: Spread value between start date and end date

Hi Lucas,

As per the example that you have discussed above, you have reference of below dates which are not part of the original data.

26/10/2012

27/10/2012

29/11/2012

-Ashish

• ###### 6. Re: Spread value between start date and end date

The dates are part of the data insofar as they are dates falling between a start date and an end date.

• ###### 7. Re: Spread value between start date and end date

Hello,

I have a business requirement where I want to use the relative filter but the current date should be a user input and based on that I should be able to select Weekly, Monthly and Quarterly view. Or is there a way to create a date as an input for the user and based on that I will have a list from which I can select weekly, Monthly or a Quarterly view.

Please help.

Regards:

Saurabh Siroya

• ###### 8. Re: Spread value between start date and end date

Hello,

I have a business requirement where I want to use the relative filter but the current date should be a user input and based on that I should be able to select Weekly, Monthly and Quarterly view. Or is there a way to create a date as an input for the user and based on that I will have a list from which I can select weekly, Monthly or a Quarterly view.

Please help.

Regards:

Saurabh Siroya