+ Reply to Thread
Results 1 to 8 of 8

if condition for dates difference

  1. #1
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    208

    if condition for dates difference

    Hi,

    Can somebody help me with if condition for the below criteria.

    if my A cell value is date then it should find the days difference between C cell date and A cell date and give me the result as number of days otherwise return the A cell value only.

    Regards,
    Vimala K

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: if condition for dates difference

    Hi,

    See if the following formula works for you.

    =IFERROR(C1-A1,A1)

    Dates in Excel are actually numbers formatted to look like dates. We can check if a cell contains number by using ISNUMBER or multiplying the cells by 1 etc., however, we cannot confirm if the number is actually a DATE without the help of VBA.

    Please go through the following URL for help on how to check if a cell value is date by using VBA

    https://answers.microsoft.com/en-us/...fd20344?auth=1

  3. #3
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    208

    Re: if condition for dates difference

    Hi
    check this

    A Column B Column C Column
    Date Result Cut off
    4/30/2017 6/30/2017
    4/30/2017
    NA
    5/18/2017
    3/22/2017
    5/25/2017

    As per the above information, I need the if formula in b column which gives the difference days of A and C column otherwise it should return NA as per A column
    Attached Images Attached Images

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,794

    Re: if condition for dates difference

    I'm not convinced that that isparticularly robust. If you can specify that the date (for example) MUST bein 2016, then a simple check to see that the value is:

    a) a number,
    b) >=42370
    c) <=42735
    Glenn



  5. #5
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,135

    Re: if condition for dates difference

    As mentioned by Glen, do you have any Upper & Lower limits for the dates?

    e.g., Dates can be say, from 2016, to 2017 or 2017 to 2018 etc.

  6. #6
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    208

    Re: if condition for dates difference

    A column dates can be of any year but C column will be the current month date. but most probably current year will be there in A column. and i need the total number of days between these two dates.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,794

    Re: if condition for dates difference

    Limited to 2017 dates, only...

    =IF(AND(A1>=DATE(2017,1,1),A1<=DATE(2017,12,31)),$C$1-A1,"NA")
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    07-23-2012
    Location
    hyderabad
    MS-Off Ver
    Excel 2016
    Posts
    208

    Re: if condition for dates difference

    Ok. Will try and get back to you if i face any challenges.

+ 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. [SOLVED] Condition Formula between dates then sum if meets between those dates
    By rhett7660 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-30-2017, 03:52 PM
  2. Difference in Dates, with Default Condition
    By bdav1216 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-24-2017, 10:41 AM
  3. Compute difference between 2 date/time columns based on a condition
    By kjsivy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-09-2013, 11:29 PM
  4. how to Calculate the difference between two times and apply <= to the condition
    By student_query in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-07-2013, 08:10 PM
  5. Replies: 4
    Last Post: 02-21-2011, 12:44 PM
  6. Replies: 0
    Last Post: 09-30-2010, 11:04 AM
  7. Calculating Time difference based on a condition
    By sonnethg in forum Excel General
    Replies: 4
    Last Post: 10-11-2005, 04:25 PM

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