Creating an excel cell that resets at the start of every month

Question

I am trying to create an Excel cell the tells me whether someone has paid rent for the month. Because rent is monthly, I would like the cell to reset to False at the start of every month and remain like so until someone changes it to true, after which it will reset again to false when theres a new month.

I have tried playing around with the Date and Time function by creating the following cell function =if(Day(Today())==1, False, True) however the obvious problem with this is that the cell doesn't remain False after the first day of the month. How should I approach this problem? Are there any other Excel Functions I should use?

Peter Vandivier · Accepted Answer · 2015-10-09T17:42:31.780

It sounds like you're looking for 2 tables.

A Ledger table showing Tenant, Payment, and Date
A Tenant Table showing summary info and letting you know if they haven't paid for the month yet

Sample Tables Below

Ledger

+--------+------------+------------+
| Tenant |  PaidDate  | PaidAmount |
+--------+------------+------------+
| Paul   | 2015-06-01 | $750       |
| Peter  | 2015-06-15 | $600       |
| Mary   | 2015-06-29 | $500       |
| Paul   | 2015-07-01 | $750       |
| Peter  | 2015-07-15 | $600       |
| Mary   | 2015-07-29 | $500       |
| Paul   | 2015-08-01 | $750       |
+--------+------------+------------+

Tenant

+--------+------------+
| Tenant |  LastPaid  |
+--------+------------+
| Peter  | 2015-07-15 |
| Paul   | 2015-08-01 |
| Mary   | 2015-07-29 |
+--------+------------+

The LastPaid column in the Tenant table is a calculated column that points back to the Ledger table with the formula =AGGREGATE(14,6,ledger[PaidDate]/(ledger[Tenant]=[@Tenant]),1) ( online info on aggregate() here, but basically this gets the most recent date from the ledger table for the tenant ). This assumes you've formatted build tables into datatables ( with ctrl + t ) and renamed them "tenant" and "ledger" ( with alt + j + t + i ).

You have a couple options after that, but the easiest is probably a conditional formatting option on the tenant table ( alt + h + l + n ). I used =$B2<DATE(YEAR(TODAY()),MONTH(TODAY()),1) when the table starts in cell A1. After all this, it ends up looking like the below

CustomConditionalFormatting .

Please feel free to comment with any questions or revise the OP with any updates. Hope this helps!

alternately, you could put the helper column "LastPaid" and the conditional formatting on the ledger table, but that would end up a bit messy, visually speaking. thought i suppose it's up to your preference, not mine. :-) — Peter Vandivier, Aug 04 '15 at 18:13

score 0 · Answer 2 · edited May 23 '17 at 11:58

I haven't tested the save/getsetting method... but it might work for your application. If you have any VBA experience, you could probably figure out any kinks that show up. If not, comment, and I'll try to help.

sub datechecker()

dim y as byte
dim x as byte



on error resume next
    y = getsetting("myapp","settings","mysetting1")
    x = day(now)
        if y > x then
            'change the range to match the cells that should be changed to false
            ThisWorkbook.Worksheets("Sheet1").Range("D1:D300") = "False"
        end if
    y = x
    savesetting ("myapp", "settings", "mysetting1", y)

end sub

links: how to modify your range if necessary: http://excelmacromastery.com/Blog/?p=165 date and time variables: http://www.classanytime.com/mis333k/sjdatetime.html assigning variables: What does the keyword Set actually do in VBA?

Creating an excel cell that resets at the start of every month

2 Answers2

Ledger

Tenant