+ Reply to Thread
Results 1 to 7 of 7

IF statement for count of entries outside of specified time

  1. #1
    Registered User
    Join Date
    10-18-2013
    Location
    Indianapolis
    MS-Off Ver
    Excel 2007
    Posts
    7

    IF statement for count of entries outside of specified time

    Trying to write an if statement giving a simple value of 1 if a time is outside of weekday hours 8am to 9pm. Cell formatted as (6/3/17 2:15 PM). Function should report "0" value as this is a Tuesday between 8am and 9pm.

    A cell formatted as (6/3/14 7:54 AM) should result in "0" value.

    Thanks!

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: IF statement for count of entries outside of specified time

    Try..

    =--(OR(HOUR(MOD(A1,1))>21,HOUR(MOD(A1,1))<8))

    A cell formatted as (6/3/14 7:54 AM) should result in "0" value.
    isn't that outside of working hours?
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    10-18-2013
    Location
    Indianapolis
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: IF statement for count of entries outside of specified time

    Correct. I meant the 7AM entry should be a value of "1".

  4. #4
    Registered User
    Join Date
    10-18-2013
    Location
    Indianapolis
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: IF statement for count of entries outside of specified time

    I believe that worked. Thank you sir!

  5. #5
    Registered User
    Join Date
    10-18-2013
    Location
    Indianapolis
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: IF statement for count of entries outside of specified time

    Sort of worked except it is excluding 8am-9PM on weekends. Weekends should always return a value of "1"

  6. #6
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: IF statement for count of entries outside of specified time

    Don't need the MOD function too..

    Try
    =--(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7,HOUR(A1)>21,HOUR(A1)<8))

  7. #7
    Registered User
    Join Date
    10-18-2013
    Location
    Indianapolis
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: IF statement for count of entries outside of specified time

    no luck. Still reporting "0" value for every other weekend date.

+ 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. Excel 2000 Count Entries within a time frame.
    By Daniel Salinas in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-10-2014, 12:10 PM
  2. Identify and count overlapping date/time entries on a call log
    By Andrew989 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-23-2012, 12:24 PM
  3. sort out specific entries from Bank statement
    By top1 in forum Excel General
    Replies: 6
    Last Post: 04-16-2010, 08:53 PM
  4. Count time (entries per hour)
    By canonelan2 in forum Excel General
    Replies: 6
    Last Post: 10-07-2008, 03:23 PM
  5. Replies: 0
    Last Post: 08-28-2005, 10:35 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