Create a formula which checks if a shift start time falls within the period 10pm to 8am

%3CLINGO-SUB%20id%3D%22lingo-sub-3281275%22%20slang%3D%22en-US%22%3ECreate%20a%20formula%20which%20checks%20if%20a%20shift%20start%20time%20falls%20within%20the%20period%2010pm%20to%208am%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281275%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20a%20workbook%20which%20has%20a%20column%20that%20gives%20the%20start%20time%20of%20a%20duty%20shift.%20I%20want%20to%20create%20a%20formula%20for%20another%20column%20which%20checks%20if%20the%20start%20time%20of%20the%20duty%20shift%20falls%20between%2010pm%20and%208am%20(a%20two%20day%20span)%2C%20and%20then%20provide%20a%20YES%2FNO%20answer%20in%20the%20new%20column.%20Does%20anyone%20have%20any%20ideas%2C%20please%3F%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3281275%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281298%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20which%20checks%20if%20a%20shift%20start%20time%20falls%20within%20the%20period%2010pm%20to%208am%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1359062%22%20target%3D%22_blank%22%3E%40CymruInThe70s%3C%2FA%3E%26nbsp%3BAttaching%20the%20file%20I%20used%20in%20my%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281294%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20which%20checks%20if%20a%20shift%20start%20time%20falls%20within%20the%20period%2010pm%20to%208am%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281294%22%20slang%3D%22en-US%22%3EOK%2C%20Riny%2C%20I'll%20try%20that.%20Thanks.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281291%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20which%20checks%20if%20a%20shift%20start%20time%20falls%20within%20the%20period%2010pm%20to%208am%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281291%22%20slang%3D%22en-US%22%3E%3CP%3EHmmm...those%20don't%20seem%20to%20work.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%2C%20maybe%20I%20should%20describe%20in%20more%20detail.%20I%20have%20in%20'Column%20A'%20a%20series%20of%20start%20times%20in%20the%20format%20'12%3A04%3A00'%2C%20in%20rows%201-27.%20I%20need%20a%20new%20column%20to%20tell%20me%20if%20any%20start%20time%20falls%20between%2022%3A00%3A00-08%3A00%3A00.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20column%20B%20(or%20somewhere%20else)%2C%20I'd%20like%20to%20create%20a%20cell%20which%20produces%20a%20YES%2FNO%20response%20if%20the%20value%20in%20Column%20A%20falls%20between%2022%3A00%3A00%20and%2008%3A00%3A00.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281284%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20which%20checks%20if%20a%20shift%20start%20time%20falls%20within%20the%20period%2010pm%20to%208am%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281284%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1359062%22%20target%3D%22_blank%22%3E%40CymruInThe70s%3C%2FA%3E%26nbsp%3BAs%20a%20variant%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202022-04-10%20at%2018.21.27.png%22%20style%3D%22width%3A%20488px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F362804i1D0F0E082D3A5F69%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202022-04-10%20at%2018.21.27.png%22%20alt%3D%22Screenshot%202022-04-10%20at%2018.21.27.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281281%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20which%20checks%20if%20a%20shift%20start%20time%20falls%20within%20the%20period%2010pm%20to%208am%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281281%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20for%20those%2C%20I'll%20try%20them.%20Thank%20you%20for%20your%20quick%20response%2C%20it's%20much%20appreciated.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3281277%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20formula%20which%20checks%20if%20a%20shift%20start%20time%20falls%20within%20the%20period%2010pm%20to%208am%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3281277%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1359062%22%20target%3D%22_blank%22%3E%40CymruInThe70s%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20start%20times%20are%20in%20D2%20and%20down.%3C%2FP%3E%0A%3CP%3EIn%20E2%20or%20another%20cell%20in%20row%202%2C%20enter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DOR(E2%3CTIME%3E%3DTIME(22%2C0%2C0))%3C%2FTIME%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%20if%20you%20prefer%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(OR(E2%3CTIME%3E%3DTIME(22%2C0%2C0))%2C%22Yes%22%2C%22No%22)%3C%2FTIME%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, I have a workbook which has a column that gives the start time of a duty shift. I want to create a formula for another column which checks if the start time of the duty shift falls between 10pm and 8am (a two day span), and then provide a YES/NO answer in the new column. Does anyone have any ideas, please?

6 Replies

@CymruInThe70s 

Let's say the start times are in D2 and down.

In E2 or another cell in row 2, enter the formula

 

=OR(E2<TIME(8,0,0),E2>=TIME(22,0,0))

 

or if you prefer

 

=IF(OR(E2<TIME(8,0,0),E2>=TIME(22,0,0)),"Yes","No")

 

Fill down.

Thank you so much for those, I'll try them. Thank you for your quick response, it's much appreciated.

Hmmm...those don't seem to work.

So, maybe I should describe in more detail. I have in 'Column A' a series of start times in the format '12:04:00', in rows 1-27. I need a new column to tell me if any start time falls between 22:00:00-08:00:00.

In column B (or somewhere else), I'd like to create a cell which produces a YES/NO response if the value in Column A falls between 22:00:00 and 08:00:00.

@CymruInThe70s Attaching the file I used in my example.