Is there a way to add a character to a time to change the formatting color, but still allow excel to user the time format in a formula?
Is there a way to add a character to a time to change the formatting color, but still allow excel to user the time format in a formula?
Hi and welcome to the forum
I think you put this in the wrong forum (Outlook?), so I will move it for you. If I am wrong, please let me know?
Also, you could use a helper to get CF to change color (use "use formula")
Perhaps if you included a few examples of what you are working with, and what you want?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thnak you for moving the thread for me.
Here is a sample of my current document. The different colors represent different shift locations. Right now we are appling the colors manually.
I am wanting a way to make excel do the coloring for us through the formulas.
I am creating a second document that will only show the shifts and not the calcuations, so that the employee's have an easier time looking at the schedule.
thank you again for your help.
Im sure we can work somethin out here. Can you supply what the different shift (ranges??) are and what color you want them?
we have two locations, a downtown campus and a main campus, we also differenciate special duties and Shift Call-off(sick).
so:
Main Campus = Green
Downtown Campus = Yellow
Special Duties = Blue
Shift Call Off = Orange
Vacation = Dark Grey
I dont see any reference to those criteria in the sample workbook? What I do see are different colors being applied to very similar (and in some cases identical) shifts…
K17= 0700 L17 = 1700 BLUE
K29 = 0000 L29 = 0800 GREEN
K33 = 0800 L33 = 1600 GREEN
K37 = 0800 L33 = 1600 BLUE
M21 = 0800 N21 = 1600 YELLOW
In order for me to help you, you need to talk me though what you do when you manually color the cells
Last edited by FDibbins; 10-09-2013 at 11:38 PM.
yes, we staff 2 officers per main campus shift and one per downtown shift. We do all entry manually. we enter the start and end times, so that excel can do the math, and then we manually change the color to represent where the officer is working. But I want to be able to add a code into the hours to tell excel to add the color for me, wihtout it confusing the math portion.
Well you could use another column for that and base the CF on that column?
The issue with that is it would add 30 more columns to the schedule.
Maybe so, but if you wanted to add a code to the time - which would make it "not" time, then you wpould probably need to add helper columns to later remove the code so you can get back t the time
Yeah, i was afraid of that.
How about, is there a way to make excel look at the color when you use a =$H$866 type formula?
not with a regular formula, no formatting (colors/fonts/bold etc) are not data, they are just cosmetic "overlays" that help us (humans) see things better, excel doesnt need them. Besides, if you added a location column, you might have a need to calc by location. You could include a small table at the top that defines the codes, and/or even have a drop-down so they are entered correctly
So if i do add an additional column to the set up, how do i tell excel to change the color of the other two columns based on the data in the one column?
example:
Column 1 Column 2 Column 3
Start Time End Time Location
OK I took your advice and am adding a location column. It works great.
Thank you
Im glad it's working well for you
Did you get the CF working the way you wanted it to?
yes, I did encounter a new problem, but I started a new thread due to it not being directly related.
Thank you
OK cool
Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks