Formula help - If a time is between 2 times in excel

[DaleJ92]

Good Afternoon All,

Firstly, many thanks in advance for any help .

What i am trying to achieve is show using the current time now(), how much time is remaining in a shift.

However i have been having some issues with my between dates coming back with hashes (Due to shifts).

So i have been trying to get this to work but my skills with excel formula are poor.

nowtime is a cell using Now() formatted to time in HH:MM:SS

This is what i have done so far for Early shift 06:00:00 - 14:00:00.

Please Login or Register  to view this content.

i was hoping for it to display a 0 or say "Not Current" if the time is not in the Early shift, instead of returning the hashes.
but im honestly not sure how to lay out the formula.

Kind regards

[Sam Capricci]

I'm not the best with time but when you use now() you are getting the DATE and time regardless of how you format it.
so the time alone for 06:00:00 is 0.25 when broken out as a number, for 14:00:00 it is 0.58. So how I would work it is more like
=IF(AND(A2>=0.25,A2<=0.58),B3-shifts!J4,"")
where A2 is your "now time" and input is like 07:15:15 instead of NOW() and then have what you want as the rest.
hope that helps.
EDIT: if you look at NOW() as a number (again, regardless of how you format it) it is 43725.31 which will never fall between those two parameters of 06:00:00 and 14:00:00 d/t the month part of it (9/17/2019 7:23:...)

[TMS]

Try:

Please Login or Register  to view this content.

[TMS]

Or, alternatively,

Please Login or Register  to view this content.

[Richard Buttrey]

Perhaps

Please Login or Register  to view this content.

[DaleJ92]

Afternoon,

Many thanks for all your replies.

TMS i have used your Second code and everything works perfect for the Early and Late Shift.

However im having issue getting it to work for the night shift, i think its because its stepping over into the second day.

22:00:00 - 6:00:00 (Night Shift hours)

Is this something that can be easily fixed :/

Kind regards

[TMS]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[TMS]

Suspect you might need to split the check in two, before and after midnight.

[DaleJ92]

Afternoon,

Many thanks for all your replies.

TMS i have used your Second code and everything works perfect for the Early and Late Shift.

However im having issue getting it to work for the night shift, i think its because its stepping over into the second day.

22:00:00 - 6:00:00 (Night Shift hours)

Is this something that can be easily fixed :/

Kind regards

[TMS]

Seems like an echo of post #6, ignoring post #7 and #8 in between.

could do with seeing some examples.

[DaleJ92]

Afternoon TMS,

I didnt repost it again.. im not sure why its done that.

i am just in the process of making the file without the sensitive info.

Kind regards

[DaleJ92]

Afternoon

Please see file attached, i have placed a note in the file to show what im trying to achieve.

Kind regards

[DaleJ92]

Evening,

I have tried a few methods even using the Mod() option but it doesnt seem to make a difference.

Anyone else have any suggestions.

kind regards