LinkBack URL
About LinkBacks
is there any way to make a conditional format for cells bassed off what time of the day they are entered in the cell?
ie 00:00 - 01:00 is one color
ect ect
any ideas?
is there any way to make a conditional format for cells bassed off what time of the day they are entered in the cell?
ie 00:00 - 01:00 is one color
ect ect
any ideas?
Time in Excel is a number, that is, a day is equal to 1 with all time in between being a fraction.
http://www.cpearson.com/excel/datetime.htm
So let's say you want to check to see if a time is between 6:00 AM and 7:00 AM
=AND($A$1>=0.25,$A$1<=0.29)
Conditional Formatting
- Highlight applicable range >> A1
- Home Tab >> Styles >> Conditional Formatting
- New Rule >> Use a formula to determine which cells to format
- Edit the Rule Description: Format values where this formula is true: =AND($A$1>=0.25,$A$1<=0.29)
- Format… [Number, Font, Border, Fill]
- OK >> OK
Place a time in a cell and then select Ctrl + Shift + 1 which will show you the underlying decimal.
HTH
Regards, Jeff
ok maby this will work....dates are not enterd into the cell..
say I add data at 2pm to a cell I want it to get formatted one way..and then in another cell I add data when its 3pm..i want it to be formatted differently
How about a sample workbook for illustration purposes (before and after)?
ok ill try that
we I dunno hwo to illustrate it
hows this
A1 = 50000
A2 = 23000
A3 = 1500
A4 = 100
Data in A1 was entered at 0600
Data in A2 was entered at 1000
Data in A3 was entered at 1500
Data in A4 was entered at 2100
each time period (ie 1 hr) a different condtional formatting would be applied based off when it was added
Okay that is making it a little clearer, but for this you will need VBA.
Do you want to go the VBA route?
ya I don't mind the VBA route...I understand vba a bit
All credit goes to Norie, he proposed the code below and after testing, it works great...
Just add the other cases for the other times. In military time... (20 = 8:00 PM)
For additional colors...
Please Login or Register to view this content.
http://dmcritchie.mvps.org/excel/colors.htm
thx so much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks