Date formula

[Jyggalag]

Hi all,

I am calculating depreciations like this:

However, my formula ONLY calculates depreciation for january if the date in column B = 01-01-2024.

Is there any way to change it, so it calculates this as long as the date in column B is in the same month as in the date mentioned in column F? and i need to pull the formula to the right to future months, so feb-24, mar-24.... jan-25.... mar-26 etc.

Would really appreciate some help!

 

[6StringJazzer]

There is no need to prefix your formula with a "+" sign.
There is no need to use the VALUE function when the cell contains a numeric value (which includes date/time values).

You have not given complete information here but if we assume that F3 contains a date (and not the text "jan-24") then the formula is:

=IF(EOMONTH($F3;0)=EOMONTH($B7;0);$E7;0)

 

[Scott Huish]

If that works you could drop the IF:

=(EOMONTH($F3;0)=EOMONTH($B7;0))*$E7

 

[6StringJazzer]

I suppose that's true. This is a matter of style, I think. I tend to prefer formulas and code that are more explicit in what they do vs. the most economical solution, unless performance becomes an overriding issue.

 

[Alex Blakenburg]

if we assume that F3 contains a date (and not the text "jan-24") then the formula is:

It looks like a table to me which means the headings are text BUT the EOMonth function seems to be converting it without any issue.

Both solutions have the referencing swapped around and the comparitive operator changed and since I am in Jeff's camp of being more explicit the end result should look like this.

=IF(EOMONTH(F$3;0)>=EOMONTH($B7;0);$E7;0)

 

[6StringJazzer]

Why do you need ">"? EOMONTH(x, 0) will return the same date for any day in a given month. If both dates are in the same month, then EOMONTH will be "=" for them.

OP had ">=" in the original formula, but that wasn't doing what OP wanted. The problem statement was " the date in column B is in the same month as in the date mentioned in column F?"

 

[Alex Blakenburg]

Hi Jeff, It must be the accountant coming out in me
Its a depreciation schedule for the year (or some future period). He will then copy across the formula to the future months so it first starts in the month in column B and then repeats in every month thereafter. Typically using the start date and depreciation period the formula would also calculate when it should stop repeating.

 

[6StringJazzer]

You're absolutely right, I did not look at the whole problem. Thanks.

 

[Jyggalag]

It looks like a table to me which means the headings are text BUT the EOMonth function seems to be converting it without any issue.

Both solutions have the referencing swapped around and the comparitive operator changed and since I am in Jeff's camp of being more explicit the end result should look like this.

=IF(EOMONTH(F$3;0)>=EOMONTH($B7;0);$E7;0)

Dear Alex,

This formula works, thank you so much!

One final request (if I may): The formula currently calculates in infinity. This means that if I set, as an example:
Depreciation start date = Jan-24
Depreciation period = 1 year

and I then post the formula to a month past Jan-25, for example, Mar-26, it will still calculate depreciation in this month, even though one year has passed.

Is there any way in which the formula can be altered to account for this?

 

[Alex Blakenburg]

Give this a try:

=IF(
   AND(EOMONTH(F$3,0)>=EOMONTH($B7,0),
       EOMONTH(F$3,0)<EOMONTH($B7,$D7*12)),
   $E7,0)