Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I had to write write a ridiculously complicated formula (I didn't want to use VBA on this occasion) to identify the next date which is the 10th of a month. The reason is that's when a payment is made into an account.

    The answer I am looking for is:

    If the date is today: 10 October 2010

    If the date is 11 October 2010: 10 November 2010

    If the date is 20 December 2010: 10 January 2011

    If the date is 10 February 2011: 10 February 2011 (or, in my dreams, "Today" but I had lost the will to live at this level of nested IF statements !)

    Is there an elegant way of extracting the date when the "10th of a month" occurs ?

    This is just for the sake of tidiness and readability of the formula.

    Many thanks

    Martin

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If the date is in A1, this seems to work:
    =DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>10),10)

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    That is simply brilliant !

    I had not appreciated that using "month 13" increments the year.

    Thanks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Not only does Month 13 (as well as any month past 12) increment the year, but days past 31 will increment the month. In the same way, days <1 will decrement the month and months <1 will decrement the year. [Day 0 is the last day of the previous month]

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Yes, I'd experimented and worked that out after your first answer - funny how one small hint can lead to many solutions, but without it you can be stuck.

    Thanks again.

Posting Permissions

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