Formula to calculate dates and amount

[wlbamc]

I am looking for a formula that will calculate the difference between 2 dates and then times that answer by the amount of money. I have an end date of the 31/01/2023 and need to know how many days between that date and 31/03/2023 and then times that by the amount the person pays in this example £100. Any help gratefully received.

 

[Joe4]

Excel actually stores dates as numbers (specifically, the number of days since 1/0/1900).
So all that dates are in Excel are numbers with a special date format applied (you can easily see this by changing the format of any date to General).
This means that to get the number of days between two dates, all you have to do is subtract them!

So then your formula structure simply becomes:

=(EndDate - StartDate) * Money

Note, if you want the date range to be inclusive, you may need to add one, i.e.

=(EndDate - StartDate + 1) * Money

For example, if the Start and End dates are the same date, would you want that to return 0 or 1 for the number of days in that situation.
If 0, use the first formula structure.
If 1, use the second formula structure.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

Fluff.xlsm
	A	B	C	D
1
2	01/01/2023	31/01/2023	100	3000
Data

Cell Formulas
Range	Formula
D2	D2	=(B2-A2)*C2

 

[wlbamc]

Excel actually stores dates as numbers (specifically, the number of days since 1/0/1900).
So all that dates are in Excel are numbers with a special date format applied (you can easily see this by changing the format of any date to General).
This means that to get the number of days between two dates, all you have to do is subtract them!

So then your formula structure simply becomes:

=(EndDate - StartDate) * Money

Note, if you want the date range to be inclusive, you may need to add one, i.e.

=(EndDate - StartDate + 1) * Money

For example, if the Start and End dates are the same date, would you want that to return 0 or 1 for the number of days in that situation.
If 0, use the first formula structure.
If 1, use the second formula structure.

Thank you, one of my dates wasn't in number format, much appreciated

 

[wlbamc]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

Fluff.xlsm
	A	B	C	D
1
2	01/01/2023	31/01/2023	100	3000
Data

Cell Formulas
Range	Formula
D2	D2	=(B2-A2)*C2

I will update my version now, and thank you, after changing my date format your formula worked perfectly

 

[Fluff]

Glad we could help & thanks for the feedback.