Results 1 to 6 of 6
  1. #1
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171

    Calculating number of days between dates

    I need to come up with a number on my form that represents the number of days from one date to and including the end of the year. I am assuming Access can do this and wondered if someone could post a formula.



    As an example, I would be looking for a formula that would tell me that the number of days from 5-1-17 through 12-31-17 is 245. The 5-1-17 date is already on the form. The 12-31-17 date would be constant.

    Ultimately, what I need to accomplish is to have that number divided by 365, giving me a decimal. I then multiply that decimal by a fixed dollar amount to get a resulting dollar amount. So, someone makes say $100,000 a year based upon 365 days/calendar year. However, they start working on 5-1-17. My form would calculate how much they would get from 5-1- thru 12-31.

    Thank you!
    Last edited by PATRICKPBME; 09-14-2017 at 09:03 AM. Reason: More Info

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If the date is actually stored as a Date (and not Text), this is pretty easy.
    Access actually stores Dates as numbers, specifically the number of days since 1/0/1900.
    So all you need to do to find the difference between two dates is subtract them.

    On your Form, if your Date field was named Date1, then you would add a Text Box to the Form, and use this formula in the Control Source of that Text Box:
    Code:
    =DateSerial(2017,12,31)-[Date1]
    Note that this will return 244. If you want the date inclusive, just add 1, i.e.
    Code:
    =DateSerial(2017,12,31)-[Date1]+1

  3. #3
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    Perfect! At the risk of becoming the bane of your existence, can you help me extend this formula out? Now that I have it working, thanks to you, to calculate# of days,I need it to next:

    1. divide the number of days by 365, then
    2. multiply that result by a certain $$$ amount that already exists in the form.

    In one specific instance, it would take the resulting number (245) and divide it by 365 resulting in .6712. I would then want it to multiply .6715 times $792,599 to have the form field display the result of $532,230.22.

    I can't seem to get my brackets/parenthesis right!

    Thanks again

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try something like:
    Code:
    =[OtherFormFieldAmount] * (DateSerial(2017,12,31)-[Date1]+1)/365
    where OtherFormFieldAmount is the name of the other fields that you want to multiply by.

  5. #5
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    Yes, indeed, that did it! Can't thank you enough. I will mark this as "solved". Thank you!

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-16-2019, 08:36 AM
  2. Replies: 5
    Last Post: 09-29-2015, 12:40 PM
  3. difference in dates: Number of days
    By akapag22 in forum Queries
    Replies: 2
    Last Post: 06-23-2015, 08:39 PM
  4. Replies: 5
    Last Post: 04-29-2014, 03:49 AM
  5. Replies: 4
    Last Post: 09-06-2011, 02:20 PM

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