0

A friend is supposed to receive $75/month from his clients.

He would like an Excel sheet, which would allow him to enter how much each client paid him during the month, and to calculate their shortfall to date.

I have tried to create a solution for him, by creating:

In Row 3, from Column 2 leftward I enter the last day of the month. In Row 4, I have in column 1: "Client 1 payment", and in column2: column13, are cells to record his client1's payments.

In Row 4 column 14, I have a formula:

=sum(if(B3<now(), B4-75, 0), if(C3<now(), C4-75, 0), if(D3<now(), D4-75, 0), if(E3<now(), E4-75, 0), if(F3<now(), F4-75, 0), if(G3<now(), G4-75, 0), if(H3<now(), H4-75, 0), if(I3<now(), I4-75, 0), if(J3<now(), J4-75, 0), if(K3<now(), K4-75, 0), if(L3<now(), L4-75, 0), if(M3<now(), M4-75, 0), if(N3<now(), N4-75, 0))

However, the total equates to 0, meaning, the IF test is failing in each cell.

Why doesn't 31/1/2015 < now() ?

Thanks.

Update: if I evaluate the formula, now() is evaluated to 42291.60678..., which doesn't compare well with "January":

enter image description here

4
  • 1
    Seems it is all right. Did you have the number 75 in your cells from B4 to N4? That also equates the formula to 0. You can use "evaluate formula" function (Formulas > Formula Auditing > Evaluate formula) to check step-by-step where did it go wrong.
    – Kenneth L
    Oct 14, 2015 at 6:29
  • No, I have empty cells in row 4. If I enter any numbers in those cells, nothing changes, so I'm assuming the IF test fails.
    – Steve
    Oct 14, 2015 at 6:33
  • @KennethL: see image above inside the question please.
    – Steve
    Oct 14, 2015 at 6:36
  • I changed "January" to 31/1/2015, and the issue remained.
    – Steve
    Oct 14, 2015 at 6:42

2 Answers 2

0

In Excel dates are kept as numbers (in fact the date is represented as the number of days since 1/1/1900) so it makes sense that NOW() evaluates to 42291.6067... The abnormal part is your cell B3 (whether showing up as "January" or "31/1/2015") is not represented as a number 42035.

Therefore please check that the number is stored correctly. Select cell B3 (or other cells containing dates) and change the number format as date (Home > Number > Short Date or Long Date). If it doesn't work, please type the date again. In any event, just to make sure that it is not in "Text" format.

9
  • Thanks Kenneth. I've confirmed the cells B3 and rightwards are formatted as short date, entered the dates again, and the Total in cell N4 (containing the long formula) is still 0.
    – Steve
    Oct 14, 2015 at 7:10
  • How does it look like when you evaluate the formula again?
    – Kenneth L
    Oct 14, 2015 at 7:25
  • The cell B3 is not evaluated as a number, it remains 31/1/15.
    – Steve
    Oct 14, 2015 at 7:28
  • Try deleting the cell contents and type it again? Is your computer configured as US date format so that it doesn't understand 31/1/15?
    – Kenneth L
    Oct 14, 2015 at 7:48
  • Deleted the cell contents, and typed the date again, confirmed it is in short date format, and "Total" cell N4 remains 0.
    – Steve
    Oct 14, 2015 at 7:51
0

There is a #REF! error in your formula. I suggest using this matrix formula to avoid lengthy and error prone formulas:

    =SUM(IF(B3:N3<TODAY(),B4:N4-75,0))

Paste this into cell O4 and finish with CTRL-Shift-ENTER (Control, Shift and Enter keys simultaneously). It will show now with curly braces around the formula.

2
  • I guess you meant =SUM(IF(B3:N3<TODAY(),B4:N4-75,0)) (note the commas instead of semi-colons), and Ctrl-Shift-Enter instead of Ctrl-Enter.
    – Kenneth L
    Oct 14, 2015 at 15:27
  • Yes, you're right, I was translating VBA from German into English. Too silly it's not English all the time. I'll fix the post. Oct 14, 2015 at 15:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .