Solved: Changing time string?

[Simon Lloyd]

Bob that's perfect when the sheet in question is the activesheet, however this sheet is never available to the user, i adjusted it to this but it fails, could you tell me what i have missed?

eFormula = "IF(OR(AND(MOD('Lookup List'!R1,1)>=--""06:15"",MOD('Lookup List'!R1,1)<=--""07:00""),AND(MOD('Lookup List'!R1,1)>=--""18:15"",MOD('Lookup List'!R1,1)<=--""19:00""))," & _
"INT('Lookup List'!R1)+ROUNDDOWN(MOD('Lookup List'!R1,1)*4,0)/4,'Lookup List'!R1-""00:15"")-" & _
"(NOT(OR(MOD('Lookup List'!R1,1)>=--""17:00"",MOD('Lookup List'!R1,1)<--""07:00"")))"

As i said earlier this has been a development progression that has seen much redundant code...etc, however, i chose to evaluate as that eformula is used and referenced elsewhere in the code for days and nights

[Bob Phillips]

My guess Simon is that the addition of the sheet name makes the string to evaluate too long, you have added another 112 characters.

[Simon Lloyd]

Hi Bob, just been messing around with the sheets, i have now changed the forumla to use Now() instead of 'Lookup List'!R1, R1 was just a time and date generated from elsewhere at the time of the task being performed, so Now() sufficed and shaved a few bytes of the workbook

Thanks for all your help, i must get you to explain the coercion and MOD if and when i get to attend any future conferences

P.S it must have been the evaluation length, i did have a look round but couldn't find something concrete on the limitation unles it follows the 255 character rule, the evaluation length was 341

[Bob Phillips]

My thought was to create a function that returned the result, where the formula was evaluated in 3 parts and then compared in VBA. Not sure whether that would still work for you as you use it elsewhere.

[Simon Lloyd]

The workaround using Now() has made me smarten more of the code up so i'm happy with that, i did think of putting it in a function but i felt it was somewhat a sledgehammer approach from my perspective as i really should be looking to reduce my code and not be so lazy!

[Bob Phillips]

Putting code in functions is not lazy, abstractiing function ality is a god practice, especially where that code is obtuse far better not to clog up a main routine, just issue a call gto the side to do that 'nasty' work quietly and return a nice tidy result.

But I fail; to see how Now works, I thought you were testing user entered values?

[Simon Lloyd]

Not too good at explaining myself Bob:
R1 is originally a concatenation of Shift, Date & Time of an action performed, whilst developing i removed the shift to leave a date & time.

Because of this thread it forced me to read over all the pages of code and follow the flow, it became apparent that i was always checking R1 and R1 was always NOW at the time of running the rest of the code which allowed me to clean up and get rid of superfluous code.

As you knidly supplied a fix for my dilemma i have also now tidied up further and no longer use the original eFormula for insertion elsewhere but copy it from the location it already resides in then make the original cell = original.value, keeping the amoubnt of volatile formula to a minimum.

You've been a great help.............see?