Add two formulas to one cell?

[Don_101]

Hi,

I have created a formula which changes the colour of a cell depending on certain inputs on the worksheet. However, I need the cell to change colour from red to green and include the phrase "complete". 

Is there a simple solution to the above issue?

The formula I use is: =IF(D5>0,IF(ISBLANK(I5),"Red","Green"),"white")

Thank you

[#2]

Any solution found?!? 🙂

@Don_101 

[#3]

@LanaMaria 

If you have a similar problem, please provide detailed information about your setup, and if possible, attach a small sample workbook without sensitive information. Alternatively, upload it to for example Google Drive, OneDrive or Dropbox and post a link to the uploaded file in a reply.

[#4]

@Don_101 -- Yes, as Hans Vogelaar indicated, I suspect no one answered because there was insufficient information -- such as how "completion" relates to other inputs (room type?), and how your formula (which will currently evaluate to the WORDS "red", "green" or "white" -- not to colors as shown) was working for you.

I *THINK* maybe what you need is to combine an "IF" test for whatever counts as completion (with the text "Complete" as the value-if-true) with conditional formatting, which would assign a green background if the cell value is "Complete". First you would set a value for the target cell that makes the cell either say "Complete" or stay blank, i.e =IF([some test], "Complete", ""). Then you would go to conditional formatting and choose "format cells that contain" (or something similar depending on your version of Excel) and set the test as 'equals "Complete" ', and set the format when true to a green cell background.

[#5]

Here goes.... I have no idea what I am doing but somehow continue to figure it out lol!! Here and YouTube are invaluable!!

Here is what I need - 

In cell Z8 is the total hours worked. In Z9 I would like it to reflect total break time. Every 5 hours I deduct 0.5.

0-4 hrs worked - 0

5-9hrs - 0.5hrs

10-14hrs - 1hr

15-19hrs worked - 1.5hrs

20-24hrs worked - 2hrs

25-29hrs worked - 2.5hrs

30-34hrs worked - 3hrs

Does that make sense? Basically I need to show the total scheduled AND total break times  - then cell AA8 would be =Z8-Z9.

https://docs.google.com/spreadsheets/d/1vjpFhJ3e4NonjMReD4CCAowMmu3RioXP/edit?usp=share_link&ouid=10...

@HansVogelaar 

[#6]

@LanaMaria 

Thanks!

In Z9:   =QUOTIENT(Z8,5)/2

Copy to Z12, Z15, etc.

[#7]

@HansVogelaar

AMAZING....but.... I just realized -

I need to take .5 off for every 5 hours works but NOT on the total at end of week. Reason is - if someone works a 3 hours shift one day, and a 7 hour shift one day it takes off 1 whole hour when in reality they did not take a break on the 3hr shift and only 30 mins on the 7 hour shift. The formula reads the total as 10 hours so deducts two breaks....

SO SORRY if I just made this more complicated!!!

MANY MANY THANKS!!!!

[#8]

@LanaMaria 

In which cells do you record the hours worked per day?

[#9]

@HansVogelaar

Here is the sheet...

https://docs.google.com/spreadsheets/d/1RVfHa3R_cZ-z_8u80jKSVk0KB06N8nvz/edit?usp=share_link&ouid=10...

Using the top employee... E7, H7, K7, N7, etc - is the hours worked each shift. If they work more than 5 hours in a shift we take off 30 mins by law for an unpaid break. I need the deductions to come off each shift individually in case the above mentioned situation happens - and it did.

If you look at row 43 (Kyle). He worked a 7 hour shift and a 3.5 hour shift which totals 10.5, but he only took a break on his 7 hour shift - no breaks between 0-4 hours of work in our province.

Is this possible? I have tried everything :S

Thank you Hans!

[#10]

@LanaMaria 

thanks!

In X8:

=SUM(QUOTIENT(E7,5),QUOTIENT(H7,5),QUOTIENT(K7,5),QUOTIENT(N7,5),QUOTIENT(Q7,5),QUOTIENT(T7,5),QUOTIENT(W7,5))/2