Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-23-2016, 10:08 AM
melvin1942 melvin1942 is offline Formula to use a date to calculate an amount $. Windows 10 Formula to use a date to calculate an amount $. Office 2007
Advanced Beginner
Formula to use a date to calculate an amount $.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 04-23-2016, 11:17 AM
jeffreybrown jeffreybrown is offline Formula to use a date to calculate an amount $. Windows Vista Formula to use a date to calculate an amount $. Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Quote:
Originally Posted by melvin1942 View Post
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
------------------------------------------------
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
Reply With Quote
  #3  
Old 04-23-2016, 03:49 PM
melvin1942 melvin1942 is offline Formula to use a date to calculate an amount $. Windows 10 Formula to use a date to calculate an amount $. Office 2007
Advanced Beginner
Formula to use a date to calculate an amount $.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default 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
Reply With Quote
  #4  
Old 04-23-2016, 03:54 PM
melvin1942 melvin1942 is offline Formula to use a date to calculate an amount $. Windows 10 Formula to use a date to calculate an amount $. Office 2007
Advanced Beginner
Formula to use a date to calculate an amount $.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 04-23-2016, 04:06 PM
jeffreybrown jeffreybrown is offline Formula to use a date to calculate an amount $. Windows Vista Formula to use a date to calculate an amount $. Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
  #6  
Old 04-23-2016, 05:05 PM
melvin1942 melvin1942 is offline Formula to use a date to calculate an amount $. Windows 10 Formula to use a date to calculate an amount $. Office 2007
Advanced Beginner
Formula to use a date to calculate an amount $.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default

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
File Type: xlsx Northpoint 8N Restoratio Financial.xlsx (29.4 KB, 8 views)
Reply With Quote
  #7  
Old 04-23-2016, 06:01 PM
jeffreybrown jeffreybrown is offline Formula to use a date to calculate an amount $. Windows Vista Formula to use a date to calculate an amount $. Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
File Type: xlsx Northpoint 8N Restoratio Financial.xlsx (34.7 KB, 7 views)
Reply With Quote
  #8  
Old 04-23-2016, 06:28 PM
melvin1942 melvin1942 is offline Formula to use a date to calculate an amount $. Windows 10 Formula to use a date to calculate an amount $. Office 2007
Advanced Beginner
Formula to use a date to calculate an amount $.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 04-23-2016, 06:40 PM
jeffreybrown jeffreybrown is offline Formula to use a date to calculate an amount $. Windows Vista Formula to use a date to calculate an amount $. Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
  #10  
Old 05-19-2016, 12:05 PM
melvin1942 melvin1942 is offline Formula to use a date to calculate an amount $. Windows 10 Formula to use a date to calculate an amount $. Office 2007
Advanced Beginner
Formula to use a date to calculate an amount $.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default 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
Attached Files
File Type: xlsx Ford 1950 8N Restore Cost.xlsx (29.4 KB, 12 views)

Last edited by melvin1942; 05-19-2016 at 03:18 PM.
Reply With Quote
  #11  
Old 05-19-2016, 03:26 PM
jeffreybrown jeffreybrown is offline Formula to use a date to calculate an amount $. Windows Vista Formula to use a date to calculate an amount $. Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
  #12  
Old 05-19-2016, 04:31 PM
melvin1942 melvin1942 is offline Formula to use a date to calculate an amount $. Windows 10 Formula to use a date to calculate an amount $. Office 2007
Advanced Beginner
Formula to use a date to calculate an amount $.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default

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
Reply With Quote
  #13  
Old 05-19-2016, 05:26 PM
jeffreybrown jeffreybrown is offline Formula to use a date to calculate an amount $. Windows Vista Formula to use a date to calculate an amount $. Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Look in cell G143...

Fix it and it works fine.

Sorry, in post #11 I realized I forgot to post the formula I used.
Reply With Quote
  #14  
Old 05-22-2016, 04:03 PM
melvin1942 melvin1942 is offline Formula to use a date to calculate an amount $. Windows 10 Formula to use a date to calculate an amount $. Office 2007
Advanced Beginner
Formula to use a date to calculate an amount $.
 
Join Date: Apr 2016
Posts: 42
melvin1942 is on a distinguished road
Default

Jeffrey, I see what you mean. I looked down through the column but I missed that. Again I thank you very much.

melvin1942
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to use a date to calculate an amount $. 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 use a date to calculate an amount $. Formula to auto calculate Day of the week based on Date prasad@dmci.ca Excel 1 11-29-2011 01:05 PM
Formula to use a date to calculate an amount $. What formula should I use to calculate commission? grs Excel 3 02-21-2011 02:17 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:09 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft