Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Formula Help (2003)

    Hi Loungers,

    I'm trying to create a formula that will calculate reimbursement $'s based on days following a training session.

    ie
    if a person leaves within one month of the date reimburse 100% of the cost of the training
    if a person leaves within three months of the date reimburse 50% of the cost of the training
    if a person leaves within six month of the date reimburse 33% of the cost of the training

    I'm sure this is not to difficult, but it has me stumped

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Help (2003)

    You can use the undocumented DateDif function to calculate the number of months between the training and the departure. See DATEDIF Function on Chip Pearson's site for its syntax. You can use the number of months in a nested IF to calculate the reimbursement percentage. See the attached workbook.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Formula Help (2003)

    Hans,

    Thanks for the quick reply - I'll give it a shot.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Help (2003)

    Don't you think its strange that Microsoft has not incorporated this (extremely useful) function into the Date & Time category of the Paste Function Palette!!! I can recall many times when I needed this function, until I recently discovered that it exists in Excel too, and not just Access!!! What is the purpose of keeping this a far off relative to the functions family???? Any idea's!
    Regards,
    Rudi

  5. #5
    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

    Re: Formula Help (2003)

    Plenty of ideas, but most lean towards the "bashing microsoft" variety so I will refrain from detailing them <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Help (2003)

    So we must just keep <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25> on! <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Help (2003)

    For consistency, the VB function (available in all VB/VBA dialects, and in expressions in Access) is DateDiff (double f), while the undocumented Excel worksheet function is DATEDIF (single F). The order of the arguments is also different, and DATEDIF accepts different values for the Interval argument, such as "ym". <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Help (2003)

    General rule of thumb:
    Acceff = FF eg. DateDiff, Iff
    Excel = F eg. DateDif, If
    Regards,
    Rudi

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Help (2003)

    IIf and DateDiff are not just for Access, they are general VB/VBA functions, available in VB6, Word VBA, Excel VBA, Access VBA, PowerPoint VBA etc.
    IF is an Excel worksheet function, but it is also a general VB/VBA keyword, available in ...

Posting Permissions

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