Overlap in no of days calculation [closed]
up vote
-1
down vote
favorite
I work at a rental company and was tasked with writing up a formula that calculates number of days in a month our various units are on rent which will be used to calculate revenue made on the unit for those days.
The report used for this duplicates the document number for each replacement unit provided to the customer and I've to calculate number of days each unit is used in the month for which I'm preparing the report.
The issue arises when the unit is replaced multiple times to a customer in the same month. There's an overlap in the number of days calculated by the formula which exceeds our systems billing criteria. Our billing criteria is based on 30 days.
The sum total in that the formula outputs here is 34 days, where as required output for this rental agreement is to be within or equal to 30 days.
Let me know if there's anyway further that I could improve on this explanation.
microsoft-excel worksheet-function
asked Dec 3 at 13:26
Herubrand
1117
closed as unclear what you're asking by Máté Juhász, fixer1234, bertieb, Pimp Juice IT, Rajesh S Dec 8 at 5:30
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
|
show 4 more comments
up vote
-1
down vote
favorite
I work at a rental company and was tasked with writing up a formula that calculates number of days in a month our various units are on rent which will be used to calculate revenue made on the unit for those days.
The report used for this duplicates the document number for each replacement unit provided to the customer and I've to calculate number of days each unit is used in the month for which I'm preparing the report.
The issue arises when the unit is replaced multiple times to a customer in the same month. There's an overlap in the number of days calculated by the formula which exceeds our systems billing criteria. Our billing criteria is based on 30 days.
The sum total in that the formula outputs here is 34 days, where as required output for this rental agreement is to be within or equal to 30 days.
Let me know if there's anyway further that I could improve on this explanation.
microsoft-excel worksheet-function
asked Dec 3 at 13:26
Herubrand
1117
closed as unclear what you're asking by Máté Juhász, fixer1234, bertieb, Pimp Juice IT, Rajesh S Dec 8 at 5:30
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
What is the meaning of your screenshots? Please pay sample data clearly indicating it's sample data. Also pay current and desired output of your formula.
– Máté Juhász
Dec 3 at 13:59
Sample data is google drive link. The desire output is to have the number of days calculated in RRWR column to be equal to RRWOR number of days.
– Herubrand
Dec 4 at 10:59
please post relevant part of your file in your question as screenshot, or format it e.g. here, questions need to be self contained.
– Máté Juhász
Dec 4 at 11:08
Added a link with relevant part. Hopefully its what is required here. TA
– Herubrand
Dec 4 at 13:17
I've inserted your pictures and improved formatting a bit too. Also tried to understand your problem. Your formula and description quite complex, and I'm still confused on the meaning of your screenshots. Calculating length of overlapping periods should not be that complex, have a look e.g. here: exceljet.net/formula/calculate-date-overlap-in-days
– Máté Juhász
Dec 4 at 13:33
|
show 4 more comments
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
I work at a rental company and was tasked with writing up a formula that calculates number of days in a month our various units are on rent which will be used to calculate revenue made on the unit for those days.
The report used for this duplicates the document number for each replacement unit provided to the customer and I've to calculate number of days each unit is used in the month for which I'm preparing the report.
The issue arises when the unit is replaced multiple times to a customer in the same month. There's an overlap in the number of days calculated by the formula which exceeds our systems billing criteria. Our billing criteria is based on 30 days.
The sum total in that the formula outputs here is 34 days, where as required output for this rental agreement is to be within or equal to 30 days.
Let me know if there's anyway further that I could improve on this explanation.
microsoft-excel worksheet-function
asked Dec 3 at 13:26
Herubrand
1117
I work at a rental company and was tasked with writing up a formula that calculates number of days in a month our various units are on rent which will be used to calculate revenue made on the unit for those days.
The report used for this duplicates the document number for each replacement unit provided to the customer and I've to calculate number of days each unit is used in the month for which I'm preparing the report.
The issue arises when the unit is replaced multiple times to a customer in the same month. There's an overlap in the number of days calculated by the formula which exceeds our systems billing criteria. Our billing criteria is based on 30 days.
The sum total in that the formula outputs here is 34 days, where as required output for this rental agreement is to be within or equal to 30 days.
Let me know if there's anyway further that I could improve on this explanation.
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Dec 3 at 13:26
Herubrand
1117
asked Dec 3 at 13:26
Herubrand
1117
edited Dec 9 at 7:14
asked Dec 3 at 13:26
Herubrand
1117
asked Dec 3 at 13:26
Herubrand
1117
asked Dec 3 at 13:26
Herubrand
1117
1117
closed as unclear what you're asking by Máté Juhász, fixer1234, bertieb, Pimp Juice IT, Rajesh S Dec 8 at 5:30
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as unclear what you're asking by Máté Juhász, fixer1234, bertieb, Pimp Juice IT, Rajesh S Dec 8 at 5:30
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
What is the meaning of your screenshots? Please pay sample data clearly indicating it's sample data. Also pay current and desired output of your formula.
– Máté Juhász
Dec 3 at 13:59
Sample data is google drive link. The desire output is to have the number of days calculated in RRWR column to be equal to RRWOR number of days.
– Herubrand
Dec 4 at 10:59
please post relevant part of your file in your question as screenshot, or format it e.g. here, questions need to be self contained.
– Máté Juhász
Dec 4 at 11:08
Added a link with relevant part. Hopefully its what is required here. TA
– Herubrand
Dec 4 at 13:17
I've inserted your pictures and improved formatting a bit too. Also tried to understand your problem. Your formula and description quite complex, and I'm still confused on the meaning of your screenshots. Calculating length of overlapping periods should not be that complex, have a look e.g. here: exceljet.net/formula/calculate-date-overlap-in-days
– Máté Juhász
Dec 4 at 13:33
|
show 4 more comments
What is the meaning of your screenshots? Please pay sample data clearly indicating it's sample data. Also pay current and desired output of your formula.
– Máté Juhász
Dec 3 at 13:59
Sample data is google drive link. The desire output is to have the number of days calculated in RRWR column to be equal to RRWOR number of days.
– Herubrand
Dec 4 at 10:59
please post relevant part of your file in your question as screenshot, or format it e.g. here, questions need to be self contained.
– Máté Juhász
Dec 4 at 11:08
Added a link with relevant part. Hopefully its what is required here. TA
– Herubrand
Dec 4 at 13:17
I've inserted your pictures and improved formatting a bit too. Also tried to understand your problem. Your formula and description quite complex, and I'm still confused on the meaning of your screenshots. Calculating length of overlapping periods should not be that complex, have a look e.g. here: exceljet.net/formula/calculate-date-overlap-in-days
– Máté Juhász
Dec 4 at 13:33
What is the meaning of your screenshots? Please pay sample data clearly indicating it's sample data. Also pay current and desired output of your formula.
– Máté Juhász
Dec 3 at 13:59
What is the meaning of your screenshots? Please pay sample data clearly indicating it's sample data. Also pay current and desired output of your formula.
– Máté Juhász
Dec 3 at 13:59
Sample data is google drive link. The desire output is to have the number of days calculated in RRWR column to be equal to RRWOR number of days.
– Herubrand
Dec 4 at 10:59
Sample data is google drive link. The desire output is to have the number of days calculated in RRWR column to be equal to RRWOR number of days.
– Herubrand
Dec 4 at 10:59
please post relevant part of your file in your question as screenshot, or format it e.g. here, questions need to be self contained.
– Máté Juhász
Dec 4 at 11:08
please post relevant part of your file in your question as screenshot, or format it e.g. here, questions need to be self contained.
– Máté Juhász
Dec 4 at 11:08
Added a link with relevant part. Hopefully its what is required here. TA
– Herubrand
Dec 4 at 13:17
Added a link with relevant part. Hopefully its what is required here. TA
– Herubrand
Dec 4 at 13:17
I've inserted your pictures and improved formatting a bit too. Also tried to understand your problem. Your formula and description quite complex, and I'm still confused on the meaning of your screenshots. Calculating length of overlapping periods should not be that complex, have a look e.g. here: exceljet.net/formula/calculate-date-overlap-in-days
– Máté Juhász
Dec 4 at 13:33
I've inserted your pictures and improved formatting a bit too. Also tried to understand your problem. Your formula and description quite complex, and I'm still confused on the meaning of your screenshots. Calculating length of overlapping periods should not be that complex, have a look e.g. here: exceljet.net/formula/calculate-date-overlap-in-days
– Máté Juhász
Dec 4 at 13:33
|
show 4 more comments
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
What is the meaning of your screenshots? Please pay sample data clearly indicating it's sample data. Also pay current and desired output of your formula.
– Máté Juhász
Dec 3 at 13:59
Sample data is google drive link. The desire output is to have the number of days calculated in RRWR column to be equal to RRWOR number of days.
– Herubrand
Dec 4 at 10:59
please post relevant part of your file in your question as screenshot, or format it e.g. here, questions need to be self contained.
– Máté Juhász
Dec 4 at 11:08
Added a link with relevant part. Hopefully its what is required here. TA
– Herubrand
Dec 4 at 13:17
I've inserted your pictures and improved formatting a bit too. Also tried to understand your problem. Your formula and description quite complex, and I'm still confused on the meaning of your screenshots. Calculating length of overlapping periods should not be that complex, have a look e.g. here: exceljet.net/formula/calculate-date-overlap-in-days
– Máté Juhász
Dec 4 at 13:33