#1
|
|||
|
|||
Formula to use a date to calculate an amount $.
I need to put a date in a column like 1/01/2016 and then use it to calculate the amount in another column. I have tried to figure this out but unable to. Thank you for your time. melvin1942 |
#2
|
|||
|
|||
Quote:
Can you share what you are trying to achieve? If you put 1/1/2016 in a cell, in terms of a number it is 42370. In Excel, time is calculated from 1/1/1900 which is equal to 1. There have been 42370 since 1/1/1900 If you have... A1 = 1/1/2016 A2 = 10/1/2016 A3 =A2-A1 The result is 274 There are 274 days between 1/1/2016 to 10/1/2016 ------------------------------------------------ Sorry, just realized I may have misread your post Maybe you want =SUMIF(A2:A20,E1,B2:B20) where A2:A20 = dates B2:B20 = numbers E1 = a date to search for |
#3
|
|||
|
|||
Formula to use a date to calculate an amount $.
What I am trying to do is to put a date like 1/1/2016 say in column A1 and another date 01/15/2016 in A3 then put an amount $300.00 in C1 and $200.00 in C3 . Then have the formula look at the dates in A1 and A3 and sum up the amount in C1 and C3. In other words it would look at the entire A column for any date that has 1/2016 in it and then find the amounts in column C that goes with that month.
I hope you under stand what I mean I am not very good at explaining what I want. Thank you for your time. melvin1942 Last edited by melvin1942; 04-23-2016 at 03:52 PM. Reason: had a wrong word |
#4
|
|||
|
|||
What I am trying to do is to put a date like 1/1/2016 say in column a1 and another date 01/15/2016 in A3 then put an amount $300.00 in C1 and $200.00 in C3 . Then have the formula look at the dates in A1 and A3 and sum up the amount in C1 and C3. In other words it would look at the entire A column for any date the that has 1/2016 in it and then find the amounts in column C that goes with that month.
I hope you under stand what I mean I am not very good at explaining what I want. Thank you for your time. melvin1942 |
#5
|
|||
|
|||
Hi Melvin,
It would help tremendously if you would attach a sample workbook illustrating what you have and your desired outcome. https://www.msofficeforums.com/faq.p...b3_attachments |
#6
|
|||
|
|||
Jeffrey I have sent you the attachment there is a note inred I hope will explain what I am trying to do.
I the you for your help. melvim1942 |
#7
|
|||
|
|||
Hi Melvin,
Well you did not say where you want these values stored. There is no doubt more than a few ways to accomplish this... See if this helps. There is a pivot table solution and a CSE solution on the 2015 tab in column AF:AG |
#8
|
|||
|
|||
Jeffrey according to what month. It will go starting in ( S4 ). If you could put the fomula in ( S4 ) then I can add them to the other months. Thank you
|
#9
|
|||
|
|||
Change all of dates beginning in S3 to 1/1/2015, U3 to 2/1/2015, and so on
Now in S4 =SUMPRODUCT(--(MONTH($I$11:$I$40)&YEAR($I$11:$I$40)=TEXT(S3,"mmy yyy")),$J$11:$J$40) copy to U4, W4 etc |
#10
|
|||
|
|||
Formula to get a month total.
What I am trying to do is to put a date like 1/1/2016 say in column G6 and another date 01/15/2016 in G7 then put an amount $300.00 in E6 and $200.00 in E7 . Then have the formula look at the dates in G6 and G7 and sum up the amount in E6 and E7. In other words it would look at the entire G column for any date the that has 1/2016 in it and then find the amounts in column E that goes with that month, and total all of that month up in the months to the right N3 and so on.
We have gone through this before and I am trying to use the formula in a different spread sheet I am sending it to you. Thank you for your time. melvin1942 Last edited by melvin1942; 05-19-2016 at 03:18 PM. |
#11
|
|||
|
|||
See how this works...
Code:
----A---- B ---C---- D ----E----- 1 1/10/2016 $359.00 1/1/2016 2 1/15/2015 $317.00 $1,913.00 3 1/30/2016 $395.00 4 1/15/2015 $284.00 5 1/4/2016 $377.00 6 1/15/2015 $217.00 7 1/11/2016 $293.00 8 1/15/2015 $366.00 9 1/1/2016 $489.00 10 1/15/2015 $206.00 |
#12
|
|||
|
|||
Jeffrey look at the attached and the formula in N4 and tell me what is wrong. There are two years starting with N4. From Jan to Oct is 2013 and from Oct, thru Dec is 2012. The formula is one that you gave me and it works will in another worksheet.
Thank you for your time. melvin1942 |
#13
|
|||
|
|||
Look in cell G143...
Fix it and it works fine. Sorry, in post #11 I realized I forgot to post the formula I used. |
#14
|
|||
|
|||
Jeffrey, I see what you mean. I looked down through the column but I missed that. Again I thank you very much.
melvin1942 |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculate amount of entered fields | sp1d3r69 | Excel | 3 | 01-26-2015 07:35 AM |
calculate date if date entered in cell, do nothing if blank | ConfuddledOne | Excel | 3 | 11-07-2014 09:37 AM |
Formula to calculate Date and Time | funkyfido | Excel | 2 | 06-25-2013 04:25 PM |
Formula to auto calculate Day of the week based on Date | prasad@dmci.ca | Excel | 1 | 11-29-2011 01:05 PM |
What formula should I use to calculate commission? | grs | Excel | 3 | 02-21-2011 02:17 AM |