Word to Equal number

[whitef]

Hi,
I work in hospitality and have created what I think is a quite complex roster/timesheet so that I can manage casual staff hours as a percentage of weekly turnover. The only thing I am struggling with is when I roster afternoon staff on, I just put 'Close' as their finish time as I can't determine their exact finish time. But obviously when the cell says Close, it's not a number that it can calculate.
So...does anyone know how I can write "Close" in the cell but that close equals 8:00:00 PM for the formulas in the sheet. I have tried GetDigit code, and that works in that it doesn't appear as 8:00:00 PM but as Close, but it then uses 'Close' in the formulas (total daily hours, total weekly hours, wage cost per week!!) which doesn't work...

Hope I'm making sense...

Thanks in advance for help and suggestions...

 

[Scott Huish]

Hi, welcome to the board.

In versions prior to XL2007, click on Insert, then Name.
In XL2007 +, Click on Formulas, then Define Name

After name, type Close

After refers to, erase what is there and put 20:00

Excel Workbook
	A	B	C
1	7:00 AM		13:00
Sheet1

 

[T. Valko]

Hi,
I work in hospitality and have created what I think is a quite complex roster/timesheet so that I can manage casual staff hours as a percentage of weekly turnover. The only thing I am struggling with is when I roster afternoon staff on, I just put 'Close' as their finish time as I can't determine their exact finish time. But obviously when the cell says Close, it's not a number that it can calculate.
So...does anyone know how I can write "Close" in the cell but that close equals 8:00:00 PM for the formulas in the sheet. I have tried GetDigit code, and that works in that it doesn't appear as 8:00:00 PM but as Close, but it then uses 'Close' in the formulas (total daily hours, total weekly hours, wage cost per week!!) which doesn't work...

Hope I'm making sense...

Thanks in advance for help and suggestions...

Create the defined name Close and assign it the value of 8:00 PM.

Name: Close
Refers to: =TIME(20,0,0)

Then you can use the name Close in formulas and it will evaluate to 8:00 PM. For example:

=Close+TIME(1,0,0)

Returns 9:00 PM

 

[mikerickson]

What I think the OP wants is to have the formula =A1 return 8:00:00 PM when the string "Close" is entered in A1.

The only way that I can think of would be to use a Change event like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim oneCell As Range
    On Error GoTo ErrorOut
    Application.EnableEvents = False
    
    For Each oneCell In Target.Cells
        With oneCell
            If LCase(CStr(oneCell.Value)) = "close" Then
                .Value = TimeValue("20:00:00")
                .NumberFormat = Chr(34) & "Close" & Chr(34)
            End If
        End With
    Next oneCell
    
ErrorOut:
    Application.EnableEvents = True
End Sub

 

[whitef]

Hi,

Thanks for the welcome messages, I forgot to welcome myself and say hi in my first post...sorry!

Ok, so the Define a name works for one cell. But if I need to use this for multiple cells spread over different the sheet (different columns) and also on various sheets, as it's saying that name exists.

Sorry, I'm pretty new to this whole excel formulas, I've used it for years, but in very basic ways, so alot of the formulas and coding is very new to me!

 

[whitef]

Thanks for this, do I go into 'Insert Function' to enter this code...

 

[mikerickson]

To use the code that I posted, right click on the sheet tab and select View Code.
When the new window appears, move the cursor to the end of the contents and copy paste the code into the window

 

[whitef]

To use the code that I posted, right click on the sheet tab and select View Code.
When the new window appears, move the cursor to the end of the contents and copy paste the code into the window

Thank you so much for that, it works a treat!

Now if anyone needs a complex roster for hospitality staff, that will work out from a roster the % staff cost then just let me know and I'd be more than happy to share.