Microsoft Office Forums Formula to use a date to calculate an amount \$.
 Register FAQ Search Today's Posts Mark Forums Read

#1
04-23-2016, 10:08 AM
 melvin1942 Windows 10 Office 2007 Advanced Beginner Join Date: Apr 2016 Posts: 42
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.

melvin1942
#2
04-23-2016, 11:17 AM
 jeffreybrown Windows Vista Office 2007 Expert Join Date: Apr 2016 Posts: 609

Quote:
 Originally Posted by melvin1942 I need to put a date in a column like 1/01/2016 and then use it to calculate the amount in another column.
Hi Melvin & Welcome to the Forum,

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

Maybe you want =SUMIF(A2:A20,E1,B2:B20)

where

A2:A20 = dates
B2:B20 = numbers
E1 = a date to search for
#3
04-23-2016, 03:49 PM
 melvin1942 Windows 10 Office 2007 Advanced Beginner Join Date: Apr 2016 Posts: 42
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.

melvin1942

Last edited by melvin1942; 04-23-2016 at 03:52 PM. Reason: had a wrong word
#4
04-23-2016, 03:54 PM
 melvin1942 Windows 10 Office 2007 Advanced Beginner Join Date: Apr 2016 Posts: 42

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.

melvin1942
#5
04-23-2016, 04:06 PM
 jeffreybrown Windows Vista Office 2007 Expert Join Date: Apr 2016 Posts: 609

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
04-23-2016, 05:05 PM
 melvin1942 Windows 10 Office 2007 Advanced Beginner Join Date: Apr 2016 Posts: 42

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
Attached Files
 Northpoint 8N Restoratio Financial.xlsx (29.4 KB, 2 views)
#7
04-23-2016, 06:01 PM
 jeffreybrown Windows Vista Office 2007 Expert Join Date: Apr 2016 Posts: 609

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
Attached Files
 Northpoint 8N Restoratio Financial.xlsx (34.7 KB, 1 views)
#8
04-23-2016, 06:28 PM
 melvin1942 Windows 10 Office 2007 Advanced Beginner Join Date: Apr 2016 Posts: 42

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
04-23-2016, 06:40 PM
 jeffreybrown Windows Vista Office 2007 Expert Join Date: Apr 2016 Posts: 609

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
05-19-2016, 12:05 PM
 melvin1942 Windows 10 Office 2007 Advanced Beginner Join Date: Apr 2016 Posts: 42
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.

melvin1942
Attached Files
 Ford 1950 8N Restore Cost.xlsx (29.4 KB, 6 views)

Last edited by melvin1942; 05-19-2016 at 03:18 PM.
#11
05-19-2016, 03:26 PM
 jeffreybrown Windows Vista Office 2007 Expert Join Date: Apr 2016 Posts: 609

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
05-19-2016, 04:31 PM
 melvin1942 Windows 10 Office 2007 Advanced Beginner Join Date: Apr 2016 Posts: 42

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.

melvin1942
#13
05-19-2016, 05:26 PM
 jeffreybrown Windows Vista Office 2007 Expert Join Date: Apr 2016 Posts: 609

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
05-22-2016, 04:03 PM
 melvin1942 Windows 10 Office 2007 Advanced Beginner Join Date: Apr 2016 Posts: 42

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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post sp1d3r69 Excel 3 01-26-2015 07:35 AM ConfuddledOne Excel 3 11-07-2014 09:37 AM funkyfido Excel 2 06-25-2013 04:25 PM prasad@dmci.ca Excel 1 11-29-2011 01:05 PM grs Excel 3 02-21-2011 02:17 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 04:12 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top