+ Reply to Thread
Results 1 to 8 of 8

Complex Formula Involving Dates & Times -

  1. 07-26-2017, 10:50 AM #1
    dshuster1
    dshuster1 is offline
    Registered User
    Join Date
    07-26-2017
    Location
    Los Angeles
    MS-Off Ver
    Mac Excel
    Posts
    3

    Complex Formula Involving Dates & Times -

    Hello,

    I hope someone can help as this is not a very easy formula. I need to extract the first and last "appointment" for each date. I would like to return a 1 for those appointments (first and last) and a 0 for any appointment in between for each day. The information is found under column B = Start. I have also duplicated this information into numbers/digits in column C. In addition, I also need a formula to do the reverse and return a 1 for "intraday" appointments while returning 0's for the first and last appointment. Thank you in advance for any help!


    Ex:

    7/3/17 8:00am = 1
    7/3/17 8:30am = 0
    7/3/17 9:00am = 0
    7/3/17 10:00am= 0
    7/3/17 11:00am = 0
    7/3/17 12:30am = 0
    7/3/17 3:00pm = 1
    7/4/17 8:30am = 1
    7/4/17 9:15am = 0
    7/4/17 2:00pm =1
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-26-2017, 10:53 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,563

    Re: Complex Formula Involving Dates & Times -

    Easily done, but what is the significance of the entry currently in M2? Can we ignore that?

    Welcome to the forum, by the way!
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-26-2017, 10:57 AM #3
    SlipEternal
    SlipEternal is offline
    Forum Contributor
    Join Date
    07-28-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    104

    Re: Complex Formula Involving Dates & Times -

    The formula to give 1 for first and last appointment and 0 otherwise (Assuming this formula is in column O):

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    The formula to give 0 for first and last appointment and 1 otherwise:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    You can add an additional filter to see if the appointment has been canceled.
    Last edited by SlipEternal; 07-26-2017 at 11:02 AM.
    Reply With QuoteRegister To Reply
  4. 07-26-2017, 10:57 AM #4
    dshuster1
    dshuster1 is offline
    Registered User
    Join Date
    07-26-2017
    Location
    Los Angeles
    MS-Off Ver
    Mac Excel
    Posts
    3

    Re: Complex Formula Involving Dates & Times -

    AliGW,

    It is of no significance and can be removed. Thank you for the welcome! I look forward to utilizing this site to help me figure out new forumlas and learn!
    Reply With QuoteRegister To Reply
  5. 07-26-2017, 11:03 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,563

    Re: Complex Formula Involving Dates & Times -

    OK, so for the first and last visits:

    =IF(COUNTIFS(A$2:A2,A2,J$2:J2,J2)=1,1,IF(COUNTIFS(A:A,A2,J:J,J2)=COUNTIFS(A$2:A2,A2,J$2:J2,J2),1,""))

    For the intra visits:

    =IF(COUNTIFS(A$2:A2,A2,J$2:J2,J2)=1,"",IF(COUNTIFS(A:A,A2,J:J,J2)=COUNTIFS(A$2:A2,A2,J$2:J2,J2),"",1))

    For this to work, change J2 to this and copy down:

    =TEXT(B2,"dd/mm/yyyy")

    or for the US:

    =TEXT(B2,"m/dd/yyyy")
    Reply With QuoteRegister To Reply
  6. 07-26-2017, 11:51 AM #6
    Estevaoba
    Estevaoba is offline
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365 Version 2412
    Posts
    1,508

    Re: Complex Formula Involving Dates & Times -

    Another way to do this:

    Array enter (ctrl+shift+enter) and drag down:

    =IF(OR(MIN(IF(DAY($B$2:$B$265)=DAY(B2),$B$2:$B$265))=B2,MAX(IF(DAY($B$2:$B$265)=DAY(B2),$B$2:$B$265))=B2),1,0)
    (for first and last)

    =IF(OR(MIN(IF(DAY($B$2:$B$265)=DAY(B2),$B$2:$B$265))=B2,MAX(IF(DAY($B$2:$B$265)=DAY(B2),$B$2:$B$265))=B2),0,1)
    (for intradays)
    Reply With QuoteRegister To Reply
  7. 07-27-2017, 01:38 PM #7
    dshuster1
    dshuster1 is offline
    Registered User
    Join Date
    07-26-2017
    Location
    Los Angeles
    MS-Off Ver
    Mac Excel
    Posts
    3

    Re: Complex Formula Involving Dates & Times -

    Hi again,

    Another question. How would I go about changing the formula =IF(COUNTIFS(A$2:A2,A2,J$2:J2,J2)=1,"",IF(COUNTIFS(A:A,A2,J:J,J2)=COUNTIFS(A$2:A2,A2,J$2:J2,J2),"",1)) to show anything EXCEPT the first appointment each day. Also if someone could break down the logic behind this formula it would be a massive help. Still trying to understand the initial formula.


    Thanks,


    David
    Reply With QuoteRegister To Reply
  8. 07-27-2017, 01:46 PM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,563

    Re: Complex Formula Involving Dates & Times -

    To mark anything except the first appointment:

    =IF(COUNTIFS(A$2:A2,A2,J$2:J2,J2)=1,"",1)

    The formula above grows as you drag down: A$2:A2 becomes A$2:A3, and so on. This means that the array being checked is growing.

    So it's checking if A2 and J2 are occurring together in a row and counting them. In this case, if that count is 1 (the first time they are counted together), it returns "" (nothing). Thereafter, whenever that combo is found, it returns 1.

    IF(COUNTIFS(A:A,A2,J:J,J2)=COUNTIFS(A$2:A2,A2,J$2:J2,J2)

    This is to find and mark the last occurrence of the combo: the logic is that the total count in the entire range will be the same as the count returned on the last row they occur together.

    I hope this helps a bit.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Indirect formula involving dates
    By hasters in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-15-2016, 11:49 PM
  2. Tutoring: Complex Formula Structure Involving the IF Function
    By lila12 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-12-2015, 11:31 AM
  3. [SOLVED] Confusion and frustration with complex formula involving ROW, INDEX & SMALL
    By JC_LA_1979 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-29-2014, 09:46 AM
  4. [SOLVED] Formula involving times not working for specific hours, e.g. 22:00
    By kenle11 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-07-2013, 11:51 PM
  5. [SOLVED] Need IF Formula involving Dates and Accumulators
    By jrosko1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-29-2012, 08:53 AM
  6. Need help with formula involving dates
    By dataslinger in forum Excel General
    Replies: 2
    Last Post: 03-16-2010, 11:57 AM
  7. Complex manipulation of aggregated dates and times ?
    By fuzioneer in forum Excel General
    Replies: 7
    Last Post: 06-13-2007, 01:15 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1