+ Reply to Thread
Results 1 to 14 of 14

Calculate difference between formatted dates in a userform

  1. #1
    Forum Contributor
    Join Date
    07-15-2016
    Location
    Maputo, Mozambique
    MS-Off Ver
    2007
    Posts
    108

    Question Calculate difference between formatted dates in a userform

    Hi everybody!
    I am trying to calculate the difference in days bewteen two dates, formatted as dd/mm/yy, in order to change the font color of the "date due" field to red, 30 days before.
    Please see the attachment... wright a due date and click the button... you will see same weird numbers as results!
    It is only right when the date due and today are the same. It mark zero...
    Why that?
    Attached Files Attached Files

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Calculate difference between formatted dates in a userform

    This works for me.
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Forum Contributor
    Join Date
    07-15-2016
    Location
    Maputo, Mozambique
    MS-Off Ver
    2007
    Posts
    108

    Re: Calculate difference between formatted dates in a userform

    Hi bakerman2... sorry but it is not working for me. If i put the date due to tomorrow i get 365 as difference...
    Last edited by Alex Piotto; 03-22-2018 at 02:13 AM.

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Calculate difference between formatted dates in a userform

    No Problem on my side.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    07-15-2016
    Location
    Maputo, Mozambique
    MS-Off Ver
    2007
    Posts
    108

    Re: Calculate difference between formatted dates in a userform

    I have to test it in another pc... maybe is another windows xp issue... as it opens i get the difference like 35 thousand etc... Thanks anyway I am sure it will work

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Calculate difference between formatted dates in a userform

    By the way, this
    Please Login or Register  to view this content.
    can more simply be
    Please Login or Register  to view this content.
    There is no need to convert from Date to String and back to Date.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  7. #7
    Forum Contributor
    Join Date
    07-15-2016
    Location
    Maputo, Mozambique
    MS-Off Ver
    2007
    Posts
    108

    Re: Calculate difference between formatted dates in a userform

    Well, i got it working on my pc BUT only if i enter the date due like mm/dd/yy.
    Unfortunately around here we use dd/mm/yy... and users will enter dd/mm/yy - so i am still testing... with poor result so far.
    Some more suggestions?

    Please Login or Register  to view this content.

  8. #8
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Calculate difference between formatted dates in a userform

    You don't need CDate(Date). Date is already a Date value, so there is no need for CDate with it.

    If your regional settings are set to use dd/mm/yy then CDate should work as it uses your regional settings. If your regional settings are set to mm/dd/yy then you really ought to change them if you like to use dd/mm/yy.

  9. #9
    Forum Contributor
    Join Date
    07-15-2016
    Location
    Maputo, Mozambique
    MS-Off Ver
    2007
    Posts
    108

    Re: Calculate difference between formatted dates in a userform

    That's the problem... i do not know the users regional settings... so i have to find a different way to do it... any idea is welcome!

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Calculate difference between formatted dates in a userform

    If you know for certain that the users will enter the date as dd/mm/yy, you might use DateSerial and Split
    Please Login or Register  to view this content.

  11. #11
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Calculate difference between formatted dates in a userform

    What if you convert them to numbers like XL does ?
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    07-15-2016
    Location
    Maputo, Mozambique
    MS-Off Ver
    2007
    Posts
    108

    Re: Calculate difference between formatted dates in a userform

    Please Login or Register  to view this content.
    The combination between the two gurus is terrific! I got it to work when users enter the date like dd/mm/yy. I will validate the field just to be sure!
    Thanks a lot to both of you.
    By the way... there is any method to check the users regional settings, so i can make a case for English or European?

  13. #13
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Calculate difference between formatted dates in a userform

    Thanks for rep+.
    Something like this.
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    07-15-2016
    Location
    Maputo, Mozambique
    MS-Off Ver
    2007
    Posts
    108

    Re: Calculate difference between formatted dates in a userform

    thanks a lot. will try after work...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to calculate the difference between two dates
    By gregory4uk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-20-2014, 03:53 PM
  2. Calculate difference in dates
    By papaexcel in forum Excel General
    Replies: 9
    Last Post: 07-27-2009, 02:43 PM
  3. Calculate difference between dates
    By paula vasile in forum Excel General
    Replies: 1
    Last Post: 09-23-2008, 06:42 AM
  4. Calculate difference between dates
    By mprice1988 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-25-2008, 01:32 PM
  5. How do I calculate the difference between 2 dates (m,d,y) ?
    By ady_sandu in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-29-2005, 01:05 PM
  6. Calculate Difference b/e 2 dates.
    By scharee in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 08-02-2005, 12:05 PM
  7. [SOLVED] Calculate difference between two dates
    By Trainer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2005, 11:06 AM

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