I need help coming up with a formula that will make a fixed value appear if a specific date falls on or before the Friday of that week.

For example:

The dates that appear other than current week are Fridays. For a fixed payment on the 25th, I'd like a formula that would make that value appear on the May 27. It doesn't need to be complicated. I plan on referencing this formula to for all the weeks because the week out dates are driven by the current week.



Any help is appreciated. Thank you!

Hi ronocco,

You can do the Friday test with a formula like:
=IF(MOD(A1,7)=6,"X","")
where A1 is the address of the cell containing the date of interest.

I'm not sure what you mean by "For a fixed payment on the 25th, I'd like a formula that would make that value appear on the May 27", though. Do you mean that you only want the 'X' to display on the preceding Wednesday?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi macropod,

Thanks for helping. I read up on the MOD formula, and I'm confused as to how this would work for what I'm trying to do?

Let me try to explain the fixed payment ordeal in a different way.

Say I have a periodic payment on the 10th. If the 12th is the Friday of the week and it contains the 10th, I want that value to appear. For other Friday's such as the 19th and the 10th doesn't fall within that week, then no value appears in that cell.

Hey macropod! I found a formula that works out except with one minor issue, when the day# is greater than the Friday#.

=IF(ABS((DAY(C$1)-10))<5,45000,"")

Can you help me with my logical issue? For example, if the Friday is the 3rd and the periodic payment date is the 30th, it doesn't work out. I want to use the OR function, but I'm not able to structure it properly.

Okay, this is the formula I came up with.

=IF(ABS((DAY(C$1)-$I19))>25,$J19,IF(ABS((DAY(C$1)-$I19))<5,$J19,""))

Can you help me simplify it? I'd like to combine finding >25 or <5.

Hi ronocco,

it would be easier to address the issue if you posted a sample workbook. As it is, I have no idea what some of your cell references relate to.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Sorry about that. I attached a sample of the spreadsheet I'm working with.

This is my current formula: IF(AND(payment date<=field date,ABS(payment date-field date)<7),payment amount,"")

Thanks!

Attached Files

Date driven value.xlsx (16.1 KB, 16 views)

Hi ronocco,

Based on B16 as the origin, you could use either:
=(DAY(B$1)<$I16)*(MOD(B$1,7)=6)*$J16
or:
=($I16<=DAY(B$1))*($I16>DAY(B$1)-7)*$J16
The first formula would output the $10.00 in column D (being the last Friday before the 10th), and the second one would output it in column E (as does your current formula).

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]