I am not new to Excel but am finding that I only know the very basics! I am trying to put together a formula that will take a fixed amount and multiply it by the number of days between two dates. More precisely, I want to find how many days between 11/1/2017 and 10/31/2019 and then multiply the resulting number of days by the amount in a particular cell. I am using this to charge a buyer for the daily interest accruing between two dates. Thank you for any help you can provide.
Welcome to the forum.
With start date in A1 and end date in A2 and the multiplier in A3:
=(A2-A1)*A3
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Welcome to the forum.
To get the number of days between two dates, just subtract them. Then multiply the result by your 'amount' cell:
= ( cell with end date - cell with start date ) * cell with amount
Note that if you want inclusive dates (that is, 1st Oct to 2nd Oct being '2' not '1') you should add one to the result of the subtraction:
= ( cell with end date - cell with start date +1 ) * cell with amount
Hope that helps.
ps please update your profile to show the version of Excel you are using, not the version of Windows - most likely 2010 or 2013 or 2016 or 365. Thank you.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
Thank you for your help. I was hoping to have just one column for the total instead of 3 for each period I am calculating. I found a similar formula on another spreadsheet but when I tried to copy it but using my information, I kept getting a popup saying my formula was too long.
This is the formula I was trying to use: =G195*DAYS(DATE(2019,10,31),DATE(2017,11,1)) Thanks again for your help.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Actually, it does not. I am still trying to use the DAYS function with the example: =G195*DAYS(DATE(2019,10,31),DATE(2017,11,1))
If anyone could help me adjust it to my situation, I would be most grateful. This formula seems to work in another workbook but when I copied it to my workbook
the popup keeps saying the formula is too long. There are many longer formulas so I must be doing something else wrong. Appreciate any help.
No problem dropping that formula into a spreadsheet here - no error message.
Which locale are you using? US?
Maybe try just this instead:
Formula:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks