Results 1 to 8 of 8
  1. #1
    assi11 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7

    Create a Date formula

    Hello,
    I need help to create a date formula as follows:
    In field "Reporting Date" I need to state that if current date equals or earlier than 10th of the month, reporting date is 18th of this month,


    If after 10th of the month, reporting date should be 18th of next month.
    Could you kindly assist.
    Best regards,
    Assi

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Consider:

    DateAdd("m", IIf(Day(Date())<11,0,1), DateSerial(Year(Date()), Month(Date()), 18))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    assi11 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Dear June7
    Thanks for reply.
    Have tried the formula, but I get response "Wrong Number of arguments"
    Could you check the formula what needs to be amended?
    Thanks assistance.
    Best regards,
    Assi

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You read too quickly. Apparently I edited my post after you read it. Had to consider change of year for December.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    assi11 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Dear June7
    Still not working correctly as all date are converted to this month (no next month reporting)
    Best regards,
    Assi

  6. #6
    assi11 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Dear June7,
    Did you send a revised formula?
    Assi

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As stated, revised in post 2. Did you re-read? The expression works for me.

    Here it is again:

    DateAdd("m", IIf(Day(Date())<11,0,1), DateSerial(Year(Date()), Month(Date()), 18))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Another possible solution (but certainly slower one)
    Code:
    DateSerial(Year(Date()), Month(Date))+ (Day(Date())>10), Min(Day(Date())),Day(DateSerial(Year(Date()),Month(Date())+2,0)))
    It returns last of next month, whenever day number in current month doesn't exist in next month (e.g January 31 results as February 28 or 29).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-16-2016, 10:52 AM
  2. Replies: 3
    Last Post: 01-21-2015, 02:40 PM
  3. Replies: 1
    Last Post: 04-04-2014, 12:35 PM
  4. Replies: 14
    Last Post: 06-21-2013, 07:18 AM
  5. Date Filtering within a Formula
    By JeanZander in forum Access
    Replies: 8
    Last Post: 10-17-2012, 07:00 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums