+ Reply to Thread
Results 1 to 3 of 3

24hr if time statement?!

  1. #1
    Registered User
    Join Date
    06-12-2019
    Location
    Perth, Australia
    MS-Off Ver
    Office 2016
    Posts
    1

    24hr if time statement?!

    Hi All,

    Im writing a time sheet for my wife and I have been trying to get a IF statement to return a value depending on what time is selected.

    The below statement for the start time works fine and returns a 2 if the start time is 6:00 and a 1 if its 7:00 in field E5

    =IF(E5=TIME(6,0,0),"2",IF(E5=TIME(7,0,0),"1","N"))


    But if i try to use the same statement for the end time, it only returns N for a false value if i have the time set to either 18:00 or 19:00

    =IF(G5=TIME(18,0,0),"2",IF(G5=TIME(19,0,0),"1","N"))


    I have tried so many permutations of different time/custom formatting for these fields and still no joy

    Capture.PNG

    Im not sure if the above pic helps give an understanding of the layout.

    Any help would be greatly appreciated.

    Thanks
    -Fiend

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: 24hr if time statement?!

    Hey. It's possible that the end time has a date component that's being excluded by the cell formatting. Try these two formulas instead:

    Please Login or Register  to view this content.
    Not ideal but dealing with times can be tricky sometimes if you're relying on Excel to store the value exactly.

    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: 24hr if time statement?!

    1. Remove the quotes from around the numbers, "2" is text, 2 is a number. You probably need numbers so remove the text.

    2. You're probably better off converting the times to hh:mm since Excel time is held as a decimal and comparisons may fail
    Change your formulas to these

    =IF(TEXT(E5,"hh:mm")="06:00",2,IF(TEXT(E5,"hh:mm")="07:00",1,"N"))

    =IF(TEXT(G5,"hh:mm")="18:00",2,IF(TEXT(G5,"hh:mm")="19:00",1,"N"))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

+ 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] Change time in text to date/time format with AM/PM converted to 24hr
    By iantix in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-14-2018, 06:13 AM
  2. [SOLVED] What formula to use in 24hr time format
    By Alexzasha in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-18-2017, 05:28 AM
  3. Adjusting time - 12hr to 24hr
    By asimbig in forum Excel General
    Replies: 5
    Last Post: 02-15-2013, 06:48 AM
  4. recognising 24hr time
    By dodger999 in forum Excel General
    Replies: 2
    Last Post: 04-23-2011, 06:21 PM
  5. time 24hr
    By ianm34 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2010, 07:34 PM
  6. Converting a time value to 24hr Clock
    By Sugar Ape in forum Excel General
    Replies: 6
    Last Post: 10-09-2009, 05:26 AM
  7. Time related to 24hr clock
    By Stig in forum Excel General
    Replies: 4
    Last Post: 06-22-2009, 02:16 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