• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating Multiple Start and End Dates

K

Kelly Monnia

New Member
I'm not sure what to call this and searched for date range topics, but couldn't find one related to my need.

We can sell items at MSRP price or a lower price. However, the item must sell at MSRP for 30 days whenever it comes off of a price change. The attached file shows the data and what I am hoping my report will look like.

You can see there is one item number, however, I will have thousands, which starts selling at MSRP on 4/1/11 and ends on 5/10/12, 406 days. Then it sells at a lower price. From 6/11/12 through 9/27/12 it sells at MSRP again. This continues through yesterday. I highlighted in red the date range that does not meet the 30 day requirement.

How do I identify each start and end date when an item is on MSRP pricing? Any help would be appreciated.

Thanks
 

Attachments

Dear Kelly, Can you provide data, before data record & after (which you required) data record.

As i imagine, between two dates (start & end date) you want to minimum MSRP price between start & end dates.

Your start date is 25-Sept 2013 & end date is 21-Oct-2013, this difference is 26 days. In your sheet you applying formula =H12-G12+1, can you explain in this formula, why you add +1?

I suggest you, for more clarity, plz provide your original data sheet with different different "Item ID", Days Dt, MSRP Price, Selling Price, where as you provide only one item id.
 
Thanks. I am trying to calculate the number of days an item is being sold at MSRP (where MSRP Price Ind = 1). I can't simply use min and max because the price fluctuates and will be sold at MSRP for many different date ranges. I'm trying to create the table in columns G:K that shows multiple start and end dates and calculates the days in between. I did this manually to show what I'm trying to create.

The number of days calculation is not the difficult part for me. It's extracting the start and end dates. Hope that makes sense. Thanks for looking at this.

Oh, I add +1 day because I'm need to include both the start and end date as the number of days.

Appreciate your help. I uploaded a new file that shows the calculation for MSRP Price Ind.
 

Attachments

You must log in or register to reply here.
Back
Top