#NAME? Error

[iaindowner]

I need to create a formula which will work out the length of a shift which is written in the following format: 9-5 or 10.30-6 or 5-CL (CL standing for close). I have all the steps in place but when I try and combine all the steps into one formula the #Name? error shows.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]Close Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 128"]12AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 454"]
<tbody>[TR]
[TD="class: xl66, width: 454"]12AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]Close[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 128"]12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 454"]
<tbody>[TR]
[TD="class: xl66, width: 454"]=LEFT(B2,(IF(LEN(B2)=3,1,2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]Shift[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 128"]11.30-6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 454"]
<tbody>[TR]
[TD="class: xl66, width: 454"]11.30-6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]Length[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 128"]7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 454"]
<tbody>[TR]
[TD="class: xl66, width: 454"]=LEN(B4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]Position of -[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 128"]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 454"]
<tbody>[TR]
[TD="class: xl66, width: 454"]=SEARCH("-",B4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]Start Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 128"]11.30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 454"]
<tbody>[TR]
[TD="class: xl66, width: 454"]=LEFT(B4,B6-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]End Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 128"]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 454"]
<tbody>[TR]
[TD="class: xl66, width: 454"]=IF(ISNUMBER(SEARCH("CL",B4,1)),B3,RIGHT(B4,B5-B6))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]Start Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 128"]11.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 454"]
<tbody>[TR]
[TD="class: xl66, width: 454"]=1*IF(ISNUMBER(SEARCH(".",B7,1)),RIGHT(B7,2)/60+LEFT(B7,2),B7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]End Time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 128"]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 454"]
<tbody>[TR]
[TD="class: xl66, width: 454"]=1*IF(ISNUMBER(SEARCH(".",B8,1)),RIGHT(B8,2)/60+LEFT(B8,2),B8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108"]Shift Length[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 128"]6.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 454"]
<tbody>[TR]
[TD="class: xl66, width: 454"]=IF(B9>B10,12-B9+B10,B10-B9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tyron]

Hey iaindowner,

Microsoft says:

Correct a #NAME? error

Occurs when Microsoft Excel doesn't recognize text in a formula.

You provided the formulas which I plugged into a sample worksheet and came up with multiple errors. Column B is what you said you had for results and Column C is the results that I got. I am not sure if I plugged in the formulas in the correct place as you did not include Row/Column information as I have in this example.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Close Time[/TD]
[TD]12am[/TD]
[TD]12am[/TD]
[TD][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Close[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]LEFT(B2,(IF(LEN(B2)=3,1,2)))[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Shift[/TD]
[TD]11.30-6[/TD]
[TD]11.30-6[/TD]
[TD][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Length[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"]LEN(B4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Positon of -[/TD]
[TD]6[/TD]
[TD]#VALUE![/TD]
[TD][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"]SEARCH("-",B4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Start Time[/TD]
[TD]11.3[/TD]
[TD]7[/TD]
[TD][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"]LEFT(B4,B6-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]End Time[/TD]
[TD]6[/TD]
[TD]#VALUE![/TD]
[TD][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"]IF(ISNUMBER(SEARCH("CL",B4,1)),B3,RIGHT(B4,B5-B6))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Start Time[/TD]
[TD]11.5[/TD]
[TD]6[/TD]
[TD][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"]1*IF(ISNUMBER(SEARCH(".",B7,1)),RIGHT(B7,2)/60+LEFT(B7,2),B7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]End Time[/TD]
[TD]6[/TD]
[TD]11.00833[/TD]
[TD][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"]1*IF(ISNUMBER(SEARCH(".",B8,1)),RIGHT(B8,2)/60+LEFT(B8,2),B8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Shift Length[/TD]
[TD]6.5[/TD]
[TD]0.5[/TD]
[TD][TABLE="width: 331"]
<tbody>[TR]
[TD="width: 331"]IF(B9>B10,12-B9+B10, B10-B9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
It may be helpful if you indicate what your starting value(s) are, what you want to happen with the data you input (step by step) and what your end result should be. VBA code may be more efficient as well if you are able to use it (Macro).

later

Ty

 

[iaindowner]

Please see the picture. The cells I edit are B2 and B4. B2 will contain anything from 12AM to 6AM and B4 will contain e.g. 9-5, 10.30-6, 4-CL.

 

[Tyron]

Please see the picture. The cells I edit are B2 and B4. B2 will contain anything from 12AM to 6AM and B4 will contain e.g. 9-5, 10.30-6, 4-CL.

Hey iaindowner,

Well as I am still getting those two Value errors then I guess we are at an impass at the moment.

I will attempt to try and make some sense of it as follows:
1) you want to enter a start time and end time in one cell.
2) you want to do so in the format of 11, 11.30 (numbers after the "." range from 1-59 to indicate minutes), CL which indicates the closing time of 12am
3) you can use helper cells to help strip out unecessary information such as "-", and to change text such as "CL" into readable data.
4) you then want to calculate the time between the start and stop times.
5) you finally want to display the length of the shift in a Hours:Minutes format.

let me know if I am close.

later

Ty

 

[iaindowner]

Spot on apart from I need the Length of Shift to appear in an hours format e.g. 10h30m to appear as 10.5

I ideally need the whole formula to be contained in one formula as I want to be able to drag and autofill for all 7 days and 40 staff.

When I replace the cell references in C11 in the picture previously attached with the respective formulas I receive the error.

 

[Tyron]

Hey iaindowner,

Okay so then you will need to have a translater to translate Hours and Minutes into Decimal Hours.

I will take a crack at it when I get back from the store. Is VBA code/Macros an option?

later

Ty

 

[iaindowner]

Yes VBA is an option however my knowledge of VBA is limited and the stuff I have done in the past has taken a lot of help. As far as I can see I have done all the steps correctly and the formulae work when all the information is split into seperate cells but when I try and combine the formulae into on formula I get the error.

Thanks.

 

[Tyron]

Hey iaindowner,

Okay great. So at least we know that is an option in the event we can't figure this out.

I was thinking earlier that maybe this has to be an array formula but I am still working the combining it into one formula. I don't think so though.

Well as my math teacher always told me ... Pay attention to the parenthesis. Those operations are done first. So I will give it a try this evening and let you know how I do tomorrow morning. Who knows, maybe I will have
it solved.

later

Ty

 

[Tyron]

Hey iaindowner,

So I worked on this thing for about 5 hours last night and I am not able to get it either.. Maybe it is just that the formula is two complex to be in one cell since it appears to need some helper cells.

There is a lot of searching using the search, len, Left, and Right functions which seems to bog it down. So I see that you have a couple options: One is to get rid of the need for all of that by having two input cells - one for start time and one for stop time. Later you can concatenate them together on a seperate sheet. Two is to create a UDF or macro to do it so you can have variables and references to text and be able to strip the stuff down easier.

I still am having issues with getting all of the single formulas to work so I will have to start from scratch. Since you haven't shown anything I am expecting that your end result will look something like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Total Hours[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tom[/TD]
[TD]8.30-CL
3.5[/TD]
[TD]5-9
4[/TD]
[TD]12-2.15
2.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 9.75[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Betty[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Rick[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
One of the issues that I see is the differentiating between am and pm hours.
later

Ty

 

[Tyron]

Hey iaindowner,

If CL = Close = 12am then what is OP = Open = ?.

later

Ty