Formula to Calaculate Cost Per Date

LearnMeExcel

Well-known Member
Joined
Aug 11, 2009
Messages
728
hi
i have this table
Excel Workbook
ABCDEF
1Information
2Cont No.Cont-ValueStart DateEnd DateDaysCost Per Day
3Cont-0014500019/06/201118/06/2012365123.288
4Cont-0022500005/02/201104/02/201236468.681
5Cont-0034800010/02/201115/09/2011217221.198
6Cont-0041200017/01/201116/01/201236432.967
7Cont-0053600025/11/201115/07/2012233154.506
8Cont-0067500008/03/201110/06/201194797.872
9Cont-0072800006/03/201105/03/201236576.712
10Cont-0084200012/02/201111/02/2012364115.385
11Cont-0092350019/12/201118/12/201236564.384
12Cont-0104800011/01/201110/01/2012364131.868
Sheet1
Excel 2010
Cell Formulas
RangeFormula
E3=D3-C3
F3=B3/E3


i want to calculate the cost for every month in 2011 and 2012 per day for every month
jan 31 day
feb 28 in 2011 and 29 in 2012
this is the other tabels
Excel Workbook
GHIJKLMNOPQR
12011
2Jan-11Feb-11Mar-11Apr-11May-11Jun-11Jul-11Aug-11Sep-11Oct-11Nov-11Dec-11
3
4
5
6
7
8
9
10
11
12
Sheet1
Excel 2010

and this is for 2012
Excel Workbook
STUVWXYZAAABACAD
12012
2Jan-12Feb-12Mar-12Apr-12May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
3
4
5
6
7
8
9
10
11
12
Sheet1
Excel 2010
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
For row 3. If you include both 19/06/2011 and 18/06/2012 then there are 366 days. Since your formula in E3 is giving 365, you must be excluding either the first day (19/06/2011) or the last day (18/06/2012) but you haven't said which. I have assumed excluding the first day. See if this formula in G3, copied across and down does what you want. It assumes G3, H3 etc actually contain the dates 1/01/2011, 1/02/2011 etc.

If this is not what you want, please give more details and perhaps some sample results for the data you have given above.

Excel Workbook
CDEFGHIJKLMNOPQRSTUVWXYZ
2Start DateEnd DateDaysCost Per DayJan-11Feb-11Mar-11Apr-11May-11Jun-11Jul-11Aug-11Sep-11Oct-11Nov-11Dec-11Jan-12Feb-12Mar-12Apr-12May-12Jun-12Jul-12Aug-12
319/06/201118/06/2012365123.288 1356.23821.93821.93698.63821.93698.63821.93821.93575.33821.93698.63821.92219.2
45/02/20114/02/201236468.6811579.72129.12060.42129.12060.42129.12129.12060.42129.12060.42129.12129.1274.73
510/02/201115/09/2011217221.1983981.66857.16635.96857.16635.96857.16857.13318
617/01/201116/01/201236432.967461.54923.081022989.011022989.0110221022989.011022989.011022527.47
725/11/201115/07/2012233154.506772.534789.74789.74480.74789.74635.24789.74635.22317.6
88/03/201110/06/201194797.8721835123936247347978.7
96/03/20115/03/201236576.7121917.82301.42378.12301.42378.12378.12301.42378.12301.42378.12378.12224.7383.56
1012/02/201111/02/2012364115.3851846.23576.93461.53576.93461.53576.93576.93461.53576.93461.53576.93576.91269.2
Distribute cost
 

thunder_anger

Board Regular
Joined
Sep 27, 2009
Messages
206
Question
if there are averages of prices every year can i take into consideration when calculating the cost per day because all the amounts are of 365 days not important being in the same year or date difference between two dates

i have a similar situation here but never solved i had to divide and isolate every year to be in a separate sheet:eek:
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Question
if there are averages of prices every year can i take into consideration when calculating the cost per day because all the amounts are of 365 days not important being in the same year or date difference between two dates

i have a similar situation here but never solved i had to divide and isolate every year to be in a separate sheet:eek:
I'm afraid I do not understand that question. Could you re-phrase it and/or provide a small set of sample data and expected results to help clarify?
 

thunder_anger

Board Regular
Joined
Sep 27, 2009
Messages
206
simply

i have here about

808 days of sales
form 3/6/2000
to 2/8/2002
the prices of 2001 is higher than 2000 with 15%
the prices of 2002 is higher than 2001 with 7%
so
when i calculate the total cost of of the 808 days
there are duplicates of a certain item so when you calculate the cost of that item in the 808 days is there a method to tell that the price has increased with previous percentage in other words (item price is an argument) so when you say
Code:
totalcost = item price (which is changing) * 808 days
so is there a way to take such an idea into consideration??
hope i am clear

[URL="http://www.excel-jeanie-html.de/index.php?f=1"][/URL]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

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