Formula to caculate the numbber of days i need to charge for using dates?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,931
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
Please help i'm getting a bit confused here.

What it is is i have two coloumns "In date" and "Out Date"

In date is column G
Out Date is Column J

So we charge whilst an item is "In stock" per day.

What I want to be able to do is instert a start date and end date and caculate how many days each row was in stock durring that period in days.

so for example:

Days in stock between
Q7S7
Column GColumn JColumn M01/06/202115/06/2021
In DateOut DateDays in stock
1/1/202027/7/202115
14/06/20211The red are the numbers i'm try to caculate
01/05/202103/06/20213
1/04/202015/07/20200
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

ChrisFoster

Board Regular
Joined
Jun 21, 2019
Messages
158
Office Version
  1. 365
Platform
  1. Windows
Just so I understand correctly, you just need a formula to know how many days are between two dates?
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,931
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi, well, its not as simple as that,
I want to add in a start date and end date, and i want the formula to tell me how many days if any that row has for that period,
so for example I might choose Start date 01/06/2021 end date 15/06/2021 and I want to caculate how long i had that row in stock between those dates by using there start date and end date as show above.
Hope that helps
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,998
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
GHIJKLMNOPQRS
6
701/06/202115/06/2021
8
901/01/202027/07/202115
1014/06/20211
1101/05/202103/06/20213
1201/04/202015/07/20200
13
Master
Cell Formulas
RangeFormula
M9:M12M9=IF(J9="",DATEDIF(MAX($Q$7,G9),$S$7,"d"),IF(OR(G9>$S$7,J9<$Q$7),0,DATEDIF(MAX($Q$7,G9),MIN($S$7,J9),"d")+1))
 

Forum statistics

Threads
1,148,284
Messages
5,745,846
Members
423,981
Latest member
ph1l

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