+ Reply to Thread
Results 1 to 3 of 3

End of Month, End of Year clear cell

  1. #1
    Registered User
    Join Date
    08-07-2004
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    62

    End of Month, End of Year clear cell

    I'm having trouble getting a handle on this for some reason.

    I have a worksheet that keeps a year to date and month to date total of overages or underages (end of day cash reconciliation). I need to have a formula that clears the month or year to date total cell at the appropriate time and I want to do this independant of the year as I generate a new sheet every day and enter yesterday's numbers.

    I think I have the year figured out by picking the month and day and comparing but the only thing I have come up with for the month is comparing to a list of 13 dates (leap year).

    Is there another way to do this that's simpler?

    A new worksheet is generated every day for yesterday. The year to date and month to date are carried foreward and moved into a cell that shows the cumulative total for the prior day. This is the cell that needs to reset to empty (or zero) if yesterday was an end of the month day.
    Last edited by itsunclebill; 02-14-2010 at 04:44 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: End of Month, End of Year clear cell

    I don't quite understand how your sheet it set up or what you're doing but the following formula will tell you whether a given cell holds the last of a month. You can probably modify this to suit your specific situation. If a day is the last day of the month, then the next day must be the 1st of the following month:

    =IF(DAY(A1+1)=1,"Given day was the last day","Given day was NOT the last day")

    If you are interested specifically in whether "if yesterday was an end of the month day" then:

    =IF(DAY(NOW())=1,"Yesterday was the last day","Yesterday was NOT the last day")

  3. #3
    Registered User
    Join Date
    08-07-2004
    Location
    Colorado
    MS-Off Ver
    365
    Posts
    62

    Re: End of Month, End of Year clear cell

    This is where staring at a screen too long makes obvious things way too hard to see. Thanks. Perfect

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1