+ Reply to Thread
Results 1 to 4 of 4

Changing the date in a cell every month

  1. #1
    Forum Contributor
    Join Date
    10-31-2010
    Location
    london
    MS-Off Ver
    Excel 365
    Posts
    150

    Changing the date in a cell every month

    Him quick question is there any way of changing a cells date automatically depending on the date,

    what im trying to do is work a money in and out spreadsheet

    i use the =today() in a1 as a date stamp and what i have is a list of payees and what date they come out, i want to use a conditional format to colour the cells once the date passes but also i want the sheet to up date each month as in

    payee 1 50 29/12/2010

    but as soon as my =today() turns to the 1/01/2011 then the payee 1 date jumps to the 29/01/2011 so on through the list of payees

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Changing the date in a cell every month

    Not sure exactly what you are looking for but try
    =DATE(YEAR(TODAY()),MONTH(TODAY()),29)
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor
    Join Date
    10-31-2010
    Location
    london
    MS-Off Ver
    Excel 365
    Posts
    150

    Re: Changing the date in a cell every month

    yes thank you that works, but now im stuck again, sorry im useless

    what i now want is if i have the =today() in cells a1 a conditional fomat on cells $D$4:$D24 which have the new formula of =DATE(YEAR(TODAY()),MONTH(TODAY()),29) then the conditional would be one this day has past then the cell would highlight green

    i thought is would just be conditional by formula and then >$A$1 applies to $D$4:$D$24 but that doesnt seem to work

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: Changing the date in a cell every month

    It should be
    "Cell Value" "less than or equal to" =$A$1

    So when A1 is >= 12/29/10, the conditional formatting takes effect. Is that what you want?

+ 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