+ Reply to Thread
Results 1 to 5 of 5

Help with Time/date calculation

  1. #1
    Registered User
    Join Date
    01-11-2017
    Location
    Hibbing, MN
    MS-Off Ver
    MS Office 07
    Posts
    22

    Help with Time/date calculation

    Sorry I don't know how else to say this. So I am attaching a sheet. I do have some formulas in there now but I have a problem. I have the dates of the enrollments across the top. I have the hire dates. I need to meet with them before 90 days but I want it to be as close to that 90 days as I can get without going over. So if they sent 90 days takes place after the meeting say someone hired on 5/16 I would meet on 7/19 because 8/23 would be after the 90 days. but if they were hired on 5/25 I would meet with them on 8/23. Let me know if that doesn't make sense. Also I would like it to not put an X in the date if I have "Termed" typed in the termed column. Any help or insight would be great.

    I know that the current formulas now are not doing what I need because some of my people are over 90 days before meeting. Also, not sure why row 27 and 32 are where they are based on hire date.

    Thanks again and ask any questions needed to understand.

    Kat
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,882

    Re: Help with Time/date calculation

    Try this formula in F2:
    =IFERROR(IF(LOOKUP($B2+90,$F$1:$Q$1)=F$1,"X",""),"")

    Copy down and across.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    01-11-2017
    Location
    Hibbing, MN
    MS-Off Ver
    MS Office 07
    Posts
    22

    Re: Help with Time/date calculation

    That worked but how do I change it to work on my actual speedsheet?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,882

    Re: Help with Time/date calculation

    Without knowing how your actual workbook is set up, can't really help you. However, formula is constructed as following.

    =IFERROR(IF(LOOKUP(Hire_Date + 90, Range Holding Meet_Date)=Meet_Date,"X",""),"")

    You can also add additional condition to leave blank for Termed in Column E. See attached sample.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-11-2017
    Location
    Hibbing, MN
    MS-Off Ver
    MS Office 07
    Posts
    22

    Re: Help with Time/date calculation

    it is the same but just a different name. I took out people names and locations that are blank on this sheet.

+ 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: 2
    Last Post: 02-03-2017, 07:39 AM
  2. Long date/short date conversion and cycle time calculation
    By COGICPENNY in forum Excel General
    Replies: 1
    Last Post: 02-14-2014, 05:17 PM
  3. [SOLVED]Time/Date calculation to check response time
    By tailz in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-01-2013, 10:20 AM
  4. Calculation of time difference between the Start date & time & End Date & time
    By Harry Jones in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-17-2012, 03:54 PM
  5. Date, Time calculation
    By velmurugan7 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-05-2009, 08:57 AM
  6. Calculation of Time/Date
    By LarryC in forum Excel General
    Replies: 2
    Last Post: 06-11-2008, 09:23 AM
  7. Ignoring Time in a Date Time Calculation
    By nmp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-23-2005, 04:35 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