+ Reply to Thread
Results 1 to 5 of 5

IF Formula for Specific Time

  1. #1
    Registered User
    Join Date
    10-28-2013
    Location
    UK
    MS-Off Ver
    2019 Home and Business
    Posts
    8

    IF Formula for Specific Time

    Hello I am trying to create an IF formula that will display either "Day", "Twilight" or "Night" based on the time of another cell.
    • It should display "Day" if Time is between 8am and 5pm. Including 8am.
    • It should display "Twilight if Time is between 5pm and 9:30pm. Including 5pm.
    • It should display "Night" if Time is between 9:30pm and 8am. Including 9:30pm

    So far I have developed this formula:

    =IF(N4="","",IF(AND(HOUR(N4)>=8,HOUR(N4)<17),"Day",IF(AND(HOUR(N4)>=17,AND(HOUR(N4)<21,MINUTE(N4)<30)),"Twilight","Night")))

    However when I enter 17:41 into N4 it displays Night.

    Please help me correct my formula.

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: IF Formula for Specific Time

    hi there. you cannot use AND(HOUR(N4)<21,MINUTE(N4)<30). that's just saying hour of N4 is less than 21. and it's true. hour of 17:41 is 17. but minute of 17:41 is 41. so MINUTE(N4) < 30 is not true. try:
    =IF(N4="","",IF(AND(HOUR(N4)>=8,HOUR(N4)<17),"Day",IF(AND(HOUR(N4)>=17,N4<--"21:30"),"Twilight","Night")))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    10-28-2013
    Location
    UK
    MS-Off Ver
    2019 Home and Business
    Posts
    8

    Re: IF Formula for Specific Time

    Sorry I thought it fixed it but it hadn't. I forgot to mention that I put the date in N4 as well. I put 10/02/2014 17:41 and the formula says it is Night.

    Please could you help me?
    Last edited by MightyCrow; 02-13-2014 at 06:24 AM.

  4. #4
    Registered User
    Join Date
    10-28-2013
    Location
    UK
    MS-Off Ver
    2019 Home and Business
    Posts
    8

    Re: IF Formula for Specific Time

    Edited the previous post because I didn't solve the problem.

  5. #5
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: IF Formula for Specific Time

    change it to:
    =IF(N4="","",IF(AND(HOUR(N4)>=8,HOUR(N4)<17),"Day",IF(AND(HOUR(N4)>=17,MOD(N4,1)<--"21:30"),"Twilight","Night")))

+ 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] Question about time formula with specific conditions
    By lizard54 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-10-2014, 04:25 PM
  2. [SOLVED] A formula to countdown dates given a time span that is specific
    By poshbikerchick in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2012, 06:07 AM
  3. Formula to not count hours before and after a specific set time period
    By Chrislawsin in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-16-2012, 06:29 PM
  4. Formula to look up value in specific time period
    By chintanbtech in forum Excel General
    Replies: 0
    Last Post: 01-17-2012, 12:44 PM
  5. Replies: 2
    Last Post: 04-27-2011, 08:21 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