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":