replace a text value with time value in formula

[andrewb90]

Hello all,

I'm a bit lost on how to go about this:

Both F10 & D10 are time values (start and end times for a work shift) Now, occasionally the F10 time will not display a time but rather say "CLOSE", is there a way for my formula to look at this as not a word but as whatever value is actually in J5?
In this example J5 would represent 23:00 so if D10 were 17:00 then the cell would return a value of "30"

=SWITCH(C10,"FOH - Day Shift","","FOH - Night Shift","","BOH - Day Shift","","BOH - Night Shift","","Prep & Maintenance","","Managers","",IF(C10="","",IF((MOD([COLOR=#ff0000]F10[/COLOR]-D10,1)*24)<Initial!$F$53,"N",IF((MOD(F10-D10,1)*24)>=Initial!$F$52,"30","15"))))

 

[AliGW]

It's not clear how J5 relates to anything! can you provide a tabular mock-up of the data?

 

[jtakw]

Hi,

Since I don't have any of your data to test, I'm only addressing that part of your formula, I also removed the quote marks around the numbers (if you want them to be numeric values rather than text):

=SWITCH(C10,"FOH - Day Shift","","FOH - Night Shift","","BOH - Day Shift","","BOH - Night Shift","","Prep & Maintenance","","Managers","",IF(C10="","",IF((MOD(IF(F10="CLOSE",J5,F10)-D10,1)*24)=Initial!$F$52,30,15)))

 

[andrewb90]

J5 is just a reference. Basically "CLOSE" can mean anytime, but If I typed 23:00 into the formula and then if it needed to be changed to 23:15, then I would have to adjust each formula instead of just changing J5. which would change all 200+ formulas that need to reference the CLOSE time.

Does that make sense? I can still do a mock up if needed

 

[AliGW]

Not to me, sorry! I need a visual on this - a tabular mock-up that can be copied and pasted into Excel would be best: you can copy and paste into your reply from your workbook.

EDIT: Having re-read your post, I think you need the INDIRECT function, but I'm not completely sure.

 

[andrewb90]

Hi,

Since I don't have any of your data to test, I'm only addressing that part of your formula, I also removed the quote marks around the numbers (if you want them to be numeric values rather than text):

=SWITCH(C10,"FOH - Day Shift","","FOH - Night Shift","","BOH - Day Shift","","BOH - Night Shift","","Prep & Maintenance","","Managers","",IF(C10="","",IF((MOD(IF(F10="CLOSE",J5,F10)-D10,1)*24)=Initial!$F$52,30,15)))

I think we are on the right track, however the formula returns "15" regardless of what value is in J5.

 

[andrewb90]

I made some tweaks to the formula that you sent and I made it work! Thank you!

=SWITCH(C10,"FOH - Day Shift","","FOH - Night Shift","","BOH - Day Shift","","BOH - Night Shift","","Prep & Maintenance","","Managers","",IF(C10="","",IF((MOD(IF(F10="CLOSE",J5,F10)-D10,1)*24)>=Initial!$F$52,"30",IF((MOD(IF(F10="CLOSE",J5,F10)-D10,1)*24)<Initial!$F$53,"N","15"))))

 

[jtakw]

You're welcome.

Read your post #6 and #7, you had a couple of extra brackets in the original formula, so here's the updated version, but if you got it to work, Great.

Again, I suggest not putting quotes around the numbers 30 and 15.

=SWITCH(C10,"FOH - Day Shift","","FOH - Night Shift","","BOH - Day Shift","","BOH - Night Shift","","Prep & Maintenance","","Managers","",IF(C10="","",IF(MOD(IF(F10="CLOSE",J5,F10)-D10,1)*24=Initial!$F$52,30,15)))