+ Reply to Thread
Results 1 to 4 of 4

if a time is between certain hours and to return a result of Business hours or After hours

  1. 07-24-2018, 03:18 AM #1
    wcngu1
    wcngu1 is offline
    Registered User
    Join Date
    12-16-2013
    Location
    Tokyo
    MS-Off Ver
    Excel 2007
    Posts
    42

    if a time is between certain hours and to return a result of Business hours or After hours

    Hi guys!

    Basically am trying to work out how to make a formula that automatically calculate if a timestamp lands between the hours of 7:30am and 4pm.

    If it falls between 7:30am and 4pm I'm hoping for Status to say Business hours, If it falls between 4pm and 7:30am I'm hoping it says After hours but it just says afterhours for everything at the moment =/

    I have attached a spreadsheet

    I have tried a few formulas with no success. e.g. =IF(AND(B2>E2,B2<F2),"TRUE","FALSE")


    Any help appreciated, I'm pretty rusty with excel after a long stint away from it.

    Cheers!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-24-2018, 03:22 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,455

    Re: if a time is between certain hours and to return a result of Business hours or After h

    The problem is column B. This is what is actually in B2:

    24/07/2018 14:16:21

    This is not just a time - it's a date and a time. In columns E and F you have times, which are all a fraction of 1 (1 being a full day = 24 hours). Every entry in column B is bigger than 1 because it has a date, not just a time. If it were just a time, then your formula would work fine.

    I hope this helps.
    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-24-2018, 03:23 AM #3
    kersplash
    kersplash is offline
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: if a time is between certain hours and to return a result of Business hours or After h

    The problem is you have the date and time in cell B2.
    Reply With QuoteRegister To Reply
  4. 07-24-2018, 03:24 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: if a time is between certain hours and to return a result of Business hours or After h

    In C1, copied down:

    =IF(AND(MOD(B2,1)>=$E$2,MOD(B2,1)<=$F$2),"Business Hours","After Hours")

    This removes the date from the B column and looks at the time portion only. You also need to "loc" the formula on start/end times, too.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    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. [SOLVED] Timesheet that calcs time in, time out, lunch used, reg hours, ot hours, double hours
    By noobface in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-21-2021, 03:45 AM
  2. Start Date + Hours to produce end date & Time using Business Hours (AND include weekend)
    By starduck in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-31-2016, 10:14 AM
  3. Time during business hours (week days) and time during Out of Hours (weekdays+weekends)
    By wintheranders in forum Excel General
    Replies: 2
    Last Post: 11-12-2014, 11:56 AM
  4. [SOLVED] Adding hours to start time - Business hours/holidays/weekends
    By Thunderer in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-21-2014, 01:09 PM
  5. Calculating OT Hours Based on Work Outside of Core Business Hours
    By dkerne in forum Excel General
    Replies: 1
    Last Post: 11-29-2011, 12:19 AM
  6. Add TAT 'x' hours with the Start Time excluding Non-business days and hours
    By Praburam in forum Excel General
    Replies: 0
    Last Post: 04-07-2011, 01:46 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