+ Reply to Thread
Results 1 to 6 of 6

Finding difference in dates are returning a true value for blank cells. advnaced

  1. #1
    Registered User
    Join Date
    06-26-2015
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    10

    Unhappy Finding difference in dates are returning a true value for blank cells. advnaced

    Below is a conversation I had with another amazing forum member on here. So far we have no been able to solve the issue, so I am posting this in hopes that you can see the progress we have made and possibly see another possibility. Basically this sheet goes haywire one week out of the year. I have a download at the bottom of the sheet I am working on. You will see the issue if you change the date at the top of page 2 to 6/27/2015 and 8/7/2015. youll see what happens if you alternate between those dates. Thank you.



    Sorry if I don't explain this well enough, it is a very odd issue.

    So I have a formula that is supposed to return a true or false answer if the weeknum date on a spreadsheet matches or is 6 months difference from the current weeknum date in cell labelled (Page2!$b$1).

    The issue is that if I use the edate function like below. What ever amount of months I put, in this case 6, it returns a "True" answer if today's date is in the 4th week of the 6 month. If I change the EDATE to add 3 months, then it will return "True" on the 4th week of the 3rd month!

    Formula is as follows:

    =OR(WEEKNUM(EDATE(Page1!F6,6))=WEEKNUM(Page2!$B$1),WEEKNUM(Page1!F6)=WEEKNUM(Page2!$B$1))

    The current date I put in is 6/27/2015. The date I'm comparing it to is a BLANK cell. It will return a "True" answer. Yet if I put in 6/15/2015 as the current date, it will return "False" like it is supposed to.

    The odd part is, if I change the EDATE function to EDATE(Page1!F6,3), the formula will return "True" if I type in 3/27/2015. When it is not supposed to. But the old 6/27 date will return the correct answer now.

    So the seems to be a correlation to the month number to the number of months I add in EDATE. GRRR. so basically one week out of the year, the whole sheet will not work.


    Last edited by jrodertogo; 08-03-2015 at 03:44 PM.



    Edit / Delete Edit Post Quick reply to this message Reply Reply With Quote Reply With Quote Multi-Quote This Message Add Reputation Report Post .

    --------------------------------------------------------------------------------


    08-03-2015, 03:24 PM #2




    daffodil11



    daffodil11 is offline Forum Expert daffodil11's Avatar
    --------------------------------------------------------------------------------
    Join Date:07-11-2013Location:Alt+F11MS-Off Ver:Excel 2010, Access 97 & 2010Posts:3,810




    Re: EDATE function comparing dates returns true for same number month



    This formula does not appear to be consistent: =OR(WEEKNUM(EDATE(Page1!F6,6))=WEEKNUM(Page2!$B$1),WEEKNUM(Page1!F6)=WEEKNUM(Page1!$B$1))

    Is this intentional?



    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!



    Quick reply to this message Reply Reply With Quote Reply With Quote Multi-Quote This Message Add Reputation Report Post .

    --------------------------------------------------------------------------------


    08-03-2015, 03:44 PM #3




    jrodertogo


    jrodertogo is online now Registered User
    --------------------------------------------------------------------------------
    Join Date:06-25-2015Location:Los Angeles, CAMS-Off Ver:2010Posts:7




    Re: EDATE function comparing dates returns true for same number month



    Oh Thank you. I'm sorry. I did type that in wrong. It is supposed to be consistent. I will correct that now. The formula is correct on the sheet. I just typed it up wrong on the forum. So help is still needed Thank you again!



    Edit / Delete Edit Post Quick reply to this message Reply Reply With Quote Reply With Quote Multi-Quote This Message Add Reputation Report Post .

    --------------------------------------------------------------------------------


    08-03-2015, 04:04 PM #4




    daffodil11



    daffodil11 is offline Forum Expert daffodil11's Avatar
    --------------------------------------------------------------------------------
    Join Date:07-11-2013Location:Alt+F11MS-Off Ver:Excel 2010, Access 97 & 2010Posts:3,810




    Re: EDATE function comparing dates returns true for same number month



    Is the issue concerning the blank cell?

    Excel counts dates as the count of days since 1/1/1900, so a blank cell is the same as 1/0/1900 = 12/31/1899. 6 Months after this date is 6/30/1900, the 26th week.

    This is less of an issue of the current date, and more an issue of the input of the blank cell.

    You could detect for its state and then act accordingly:

    =IF(B2="",FALSE,OR(WEEKNUM(EDATE(B2,6))=WEEKNUM($B$5),WEEKNUM(B2)=WEEKNUM($B$5)))


    Last edited by daffodil11; 08-03-2015 at 04:07 PM.



    Quick reply to this message Reply Reply With Quote Reply With Quote Multi-Quote This Message Add Reputation Report Post .

    --------------------------------------------------------------------------------


    08-03-2015, 04:13 PM #5




    jrodertogo


    jrodertogo is online now Registered User
    --------------------------------------------------------------------------------
    Join Date:06-25-2015Location:Los Angeles, CAMS-Off Ver:2010Posts:7




    Re: EDATE function comparing dates returns true for same number month



    Yes the issue is with if the cell is blank. I will try that formula when I get home.

    I did not know that. I appreciate the learning lesson there. That sounds like my issue. It happens in the 26th week alright.



    Edit / Delete Edit Post Quick reply to this message Reply Reply With Quote Reply With Quote Multi-Quote This Message Add Reputation Report Post .

    --------------------------------------------------------------------------------


    08-04-2015, 01:19 AM #6




    jrodertogo


    jrodertogo is online now Registered User
    --------------------------------------------------------------------------------
    Join Date:06-25-2015Location:Los Angeles, CAMS-Off Ver:2010Posts:7




    Re: EDATE function comparing dates returns true for same number month



    Thank you for the recommendation daffodil11, but sadly it didn't work. I have attached a basic copy of the sheet I am working on. Edited to have personal info removed. You will see on the workbook that I have a page of dates for when people were signed off on training, and the second page was a calculation page to come back with a true or false answer if todays date is time for the 6 month refresher for their training from the date they were signed off. I did work week so that it would remind me for the whole week rather than just one day. I will be using these true false statements to create various lists on a 3rd page.

    When you open it, you will see how I want it to work. as today's date will work, but to see the issue I am having, just change the date in Page2!B1 to 6/27/2015 and youll see the changes on the first page. This issue renders the whole workbook useless on the 26th week of every year.
    Attached Files Attached Files

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Finding difference in dates are returning a true value for blank cells. advnaced

    When you apply a date function to a blank cell...Excel interprets that cell as zero. The date of zero is effectively Dec 31, 1899.
    And six months from that date is Jun 30, 1900 <-Week 26

    In your posted workbook, try this formula, copied across and down:
    Please Login or Register  to view this content.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Finding difference in dates are returning a true value for blank cells. advnaced

    Boy was I overthinking that..
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  4. #4
    Registered User
    Join Date
    06-26-2015
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    10

    Re: Finding difference in dates are returning a true value for blank cells. advnaced

    I will try it when I get back to the office in 2 hrs. Thank you! You are so kind!

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,601

    Re: Finding difference in dates are returning a true value for blank cells. advnaced

    And in future, rather than quote whole threads, just provide a link to them - select the thread that you want to link to, click in your browser address bar, then CTRL-C, then when you are composing your post do CTRL-V to paste the link in.

    Pete

  6. #6
    Registered User
    Join Date
    06-26-2015
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    10

    Re: Finding difference in dates are returning a true value for blank cells. advnaced

    @Daffodil11. Thank you so much!!!! It worked! I appreciate it a lot. You are a life saver! Thank you!

    And @Pete_UK. My apologies. I will do that next time. This was my first issue that I posted here. I appreciate the advice!

    Have a wonderful day guys!

+ 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. Replies: 3
    Last Post: 11-06-2014, 01:00 PM
  2. Replies: 8
    Last Post: 08-04-2014, 01:45 PM
  3. [SOLVED] Finding the difference between two dates and times
    By Harper95th in forum Excel General
    Replies: 8
    Last Post: 02-14-2014, 02:53 PM
  4. [SOLVED] Returning most recent dates with conditions where some fields are blank
    By connmtc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-27-2013, 06:06 PM
  5. Finding difference in dates for repeat entries
    By strikinglyfamiliar in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-27-2012, 03:21 PM
  6. Finding Occurences of "True" and returning the Correlating Description
    By braydon16 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-23-2011, 03:56 PM
  7. Replies: 6
    Last Post: 10-27-2005, 11:05 AM

Tags for this Thread

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