How to have a date appear instead of a designated value?

[jneiderman82]

Good morning all,

I was curious if someone could lead me in the right direction? I was given the task by my manager to put together a spreadsheet that tallies up the amount of time someone spent in a certain classroom.

They want the amount of time, 2 or 4 to be color coded but they do not want the numbers 2 or 4 to appear in the cell. Instead, they would like the date to appear as 9/12, 9/13 etc, no need for the year.

The tricky part is that the formula I am using is adding up all the seat time so 2 and 4 work perfectly and the dates would just throw off my formula. Any ideas?

To be honest too, I used Google Sheets and now my formula for a drop down option isn't listed but appears in Excel once you type in "2 or 4"

I prefer to use Google Sheets as the principal is trying to share this with the school.

Please see attached for the Excel spreadsheet.

[Richard Buttrey]

Where does the date come from and where on your sheet do you want it to appear?

Please upload a workbook and show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[jneiderman82]

I wanted the date to appear in the cell where the student sat for either 2 or 4 hours. The teacher could input the designated colors and the date would appear, instead of the numbers 2 or 4 with their respective colors.

I've been working on this and definitely hit a wall. I attached the updated spreadsheet with some examples as to what I was trying to do, as you will see the date that is listed messed up the entire formula when it came to tallying up the results for the week.

Hope this is a better explanation than my first post.