Count Unique Days Of an Item Between 2 dates which is larger than 1

Arturs

New Member
Joined
Dec 26, 2018
Messages
27
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have been trying to figure out a formula to see which materials are missing in the production for more than 1 day.

I have a 3 column report which I get sent every month - Start date, End date and Item Nr. (sheet1, columns A, B and C). I have formatted to short date columns A and B, but they do have time included as well.

Sheet11.PNG


In sheet2 I have created a Table :

Column A "Item Numbers" where I have removed all duplicates for Item Nr. from sheet 1
Column B - Date beginning 2021.01.01

Sheet2.PNG


I have been asked to try to create a report, where I can see how many unique days, an Item has been missing every month.
A missing item in production does not count as missing, if it is missing for one day (less than 24hrs).

In my previous attempt I created a column which had a number of days missing, but I could only manage to sum up the total amount of days per month. Could not find the formula to calculate unique days.

prev.att.png


Ideally, in sheet2 cell B2 I would like to have 12 days.

Hope you can help, as I`m stuck for a couple days now :(
 
I'm running out of ideas.

Try clearing the formulas from the source table and re-entering them (Duration, Adjusted Start and Adjusted End columns). Although unlikely, it could be that one, or more of them is looking at the wrong column with the additional columns.
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
I'm running out of ideas.

Try clearing the formulas from the source table and re-entering them (Duration, Adjusted Start and Adjusted End columns). Although unlikely, it could be that one, or more of them is looking at the wrong column with the additional columns.

Thank you , Jason!

I`ve used the =clean formula on duratation and added another column, looks like that was the case.

Sorry to reply this late, you helped me out a lot!
 
Upvote 0

Forum statistics

Threads
1,224,112
Messages
6,176,432
Members
452,728
Latest member
mihael546

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
Back
Top