Hi! I have been using an if function to check if a time value is between two other times. The formula I've been using is =if(and(B4>=C5;B4<=C6);"true";"false") with C5 containing the start value, C6 the end value and B4 the time value that I want to check. So for example, if I want to know if the 00.10 (which is B4 in this case) falls between 23.40 (which is C5 in this case) and 01:00 (C6 in this case), it says false but I want it to say true. Anyone that knows an easy formula to fix this?
Last edited by helloxxx; 07-22-2019 at 05:20 AM.
Try this:
=MOD(B1-A1,1)
where A1 is the start time and B1 the finish time.
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.
Not sure how you mean, to put the mod(B1-A1,1) in the if function or just like you wrote it? Beacuse when I type in what you wrote I only get 01:00 back.
Did you adapt it to suit your cells?
=MOD(C6-C5,1)
What is your expected outcome?
Can you attach a sample workbook? It's not entirely clear what you are trying to achieve.
Sorry, I edited my first post to clarify. My expected outcome is for the formula to say "true" when I check if a value like 00:10 is between two values that are across midnight, for example if 00:10 is between 23.40 and 01.00. But it says false. Tried to attach a workbook but it doesn't work.. maybe you understand better now?
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
You've been asked for a workbook - instructions above in post #6.
Your duplicate thread has been closed.
Here is a sample excel file, let me know if there's something you dont understand!
Bumping for visibility.
Please be patient.
If you have further queries around this problem, post them here.
You have now tried three times to open duplicate threads, which is not allowed. Please do not open any further threads on this same issue - continue here in this thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks