Hello all,
I need some assistance with a two-fold issue. I need a formula to calculate the age (in hours) of how long a particular case has been open (calculated in column E) and color the row according to the color coded legend below the rows. Currently, the conditional formatting that is there only accounts for a date and now I need to report how many hours old a particular item is. I have attached an example spreadsheet for reference.
Any assistance would be greatly appreciated.
remove duplicated
Last edited by etaf; 10-26-2017 at 03:12 PM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
it works - if you put dates into those cells
rather than use N3
you could replace that for today()
also for green rather than = 2
use >=2
then you can delete the other green
If the cell is blank , what do you want the colour to be
you could ADD
AND ( ..... , D4<>"")
Now time
you have NO time in the cell so did you just want multiples of 24
you could then use today() - D4
which will give the number of days
and then * 24
to get hours
is that the sort of thing you mean ?
Try this
apply green color to B4:E15
=TODAY()-$D4<3 --> Green (0, 176, 80) (base color not needed in conditional formating)
=TODAY()-$D4=3 --> Yellow (255, 255,0)
=TODAY()-$D4=4 --> Orange (255, 204, 0)
=TODAY()-$D4=5 --> Dark Orange (255, 153, 51)
=TODAY()-$D4>5 --> Red (255, 255, 255)
Apply formulas into B4:E15
See the file
Thank you for the response. My biggest problem aside from that is the need for the cell D4 to have a date and time, like 10/24/2017 11:42 AM and then for E4 to calculate the hours (I assume the best way would be using the NOW() function) to tell me how many hours it has been open. Any suggestions would be helpful.
yes NOW() will have the system time
and update each time the spreadsheet calculates
also if its over 24 you need to format the time cells as [H]:MM
then you will get hours above 1 day
Thank you all, that has solved my dilemma.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks