How can I make a word represent a time?

[1and1are5]

I'm creating a schedule for work and I want to be able to calculate the hours scheduled for the week. So I have something that looks like this {=SUM(F4-E4,I4-H4,L4-K4...)} However, I would like to enter the word CLOSE and have it represent lets say 9:30 pm or 21:30.

[protonLeah]

One easy way is to select a cell in an unused area of the sheet and put "Close" in the name box. Then, in the cell, put in your current close value. Use "Close" in the formula instead of the cell address.

[1and1are5]

Alright now what do I do to make my formula recognize that when I type CLOSE it needs to be associated with that time?

[DonkeyOte]

You could perhaps combine protonLeah's suggestion with your implied layout ?

Instead of typing "Close" into F4, I4 etc... type in: =Close
At which point F4, I4 etc will populate with the Close Time as specified in the Named cell (eg 21:30)

If you don't want to do that then you need to adapt your aggregation formula (obviously) to account for the fact that some cells are Text rather than Number,e.g.:

Please Login or Register  to view this content.

Note the Array entry requirement - if the Array has been set correctly the above will appear encased within { } - these can not be typed in manually.

[protonLeah]

Explain what you did. I hope you didn't put "close" in a cell...?
Post your workbook, or a sample showing what you did so far.

[1and1are5]

This is what I have so far. Yes, I need the formula to recognize numbers and text. So that it can be read as CLOSE but still have it represent a number so that it can be calculated.

[cody_o]

Was there ever a solution for this question?

[bebo021999]

I think this should works:

Please Login or Register  to view this content.

[cody_o]

I think this should works:

Please Login or Register  to view this content.

That's great, it does work! Not entirely sure why it works but it does! Thanks! Now i just gotta figure out how to incorporate that into the equation i already have and hope it doesn't go over the character limit.