Datedif with several entries

ddlyturtle

New Member
Joined
Jan 11, 2019
Messages
3
Hi,

I have been scratching my head for some time now trying to figure out how to layout my table structure. I have one table that gets data of sales imported from several databases manually but salesperson is a related key so now connected to a second table to track if the salesperson is a homeseller and what brand he/she focuses on etc. Just to expand potential ways for statistics.
eg.

DateSalespersoncityproductsalestotal
2015-01-02AdamLondonCatnip100

<tbody>
</tbody>



SalespersonLocationEmployeePeriodBrand
AdamHome2015-01-01Whiskers
BenAway2016-03-01Skittles

<tbody>
</tbody>





However, I want each salesperson to have something i now call "EmployeePeriod". As they are employed elsewhere and they might change brand but i do not get this information from any imported data. So i'll have/want to manually edit this table as changes will rarely happen, instead of manually entering this to every import(which contains 60k rows).

I want the historic data to stay related to that persons row as it was related to that period. (eg. Adam sells Whiskers for 2015-01-01 - 2016-01-01) while 2016-01-02 the brand was changed to Skittles.

so for all sales from the date 2016-01-02 Adams sales will be labeled skittles, and all data for 2015-01-01 - 2016-01-01 will be still be categorized as Whiskers.


Problem 2:
With the same current tables, i'd like to track how many days that salesperson was selling whiskers depending on which date i am looking at in the pivot. To compare if numbers of sales increased with time spent within that period/brand. Can't figure a clever way to solve this.

Eg in pivot if i filter the dates between 2015-01-01 and 2015-12-31 i'll see a column with linear datedif up until a new "employeeperiod" starts. something like (=Datedif(Table1!Date, Table2!EmployeePeriod,"d") but when employeeperiod changes it becomes troublesome to somehow keep the historical data to previous employeeperiod.


I'm open for suggestions..
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Watch MrExcel Video

Forum statistics

Threads
1,127,630
Messages
5,625,981
Members
416,148
Latest member
artcank

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top