Weekend & Autofill

[bigtmovers]

I'm trying to find out the forum for two things

1. Weekend: How can cell C41 check if cell A5 is Monday thru Saturday and if so it would equal whatever cell U45. What would be the formula for this?
2. Autofill: How to autofill Cell C42 Total Net yellow if its not 1/3rd. Total Income Cell C39 - Work Labor Cell C40- Office Labor Cell C41 = Total Net Cell C42. Formula?

[JohnTopley]

I would look carefully at the format of your spreadsheet as it is not very "Excel"friendly with many merged cells (to be avoided as they cause nothing but problems) and with data not set out neatly in columns and rows.

Why not columns with:

Date
Truck no
Time In
Time out
Labor cost
Truck Total

etc

[bigtmovers]

John,

Thats because we have up to 5 Trucks that go out in single day. We need detailed information for each truck. I have not had problems running over 1 year now it works fine. I just don't know the formula to some stuff.

[JeteMc]

Hello BigTMovers and Welcome to Excel Forum.
As to the formula for C41 try: =IF(AND(WEEKDAY(A5,2)>=1,WEEKDAY(A5,2)<=6),U45,0)
I am confused about what you are asking in point 2. C42 already contains a formula, and it sounds as if you would like to use conditional formatting if a condition is not met (1/3rd), however I don't know what it should, or I guess should not, be 1/3 of?
Let us know if you have any questions.

[bigtmovers]

JeteMc,

1. Thank you so much for the formula but when I tried that formula on Sunday cell C82 it still counted the as 300.
2. C42 that it correct I'm trying to change the color of cell C42 if is not met 1/3rd of what cell C39 & C40. So if you add cell C39 & C40 then cell C42 needs to be more than 1/3rd of that amount, if its not it needs to change color or get highlighted.

[JeteMc]

Past the formula into cell C41 and press Ctrl + Enter,
While C41 is still selected press Ctrl + c,
Select C82 and press Ctrl + v then press Enter. (I get $0.00 as a result)
Please set up a scenario in which the Total Net should change color, I notice that both total income and work labor are the sum of several cells and that some of them also are populated by formulas.I also notice that as of now C42 is -$300 which is less than 1/3 of zero, so I just want to make sure that I understand the scenario before attempting the conditional formatting formula.
Let us know if you have any questions.

[bigtmovers]

Jete,

1. The codes works thank you very much
2. I have uploaded a file where Total Net Cell C42 should change color. Cell C39 Total Income is $1000 so if Total net was more than $333.33 1/3rd it wont change color. Since its not 1/3rd it needs to be red.

[FlameRetired]

Another way for C41 not array entered.

Please Login or Register  to view this content.

[FlameRetired]

In the attached. Conditional format is applied for formula in both. Dark orange text if

Please Login or Register  to view this content.

is true. Dark red if

Please Login or Register  to view this content.

is true.

[bigtmovers]

Flame,

That works perfectly thank you so much but how do I apply or use the formula for other cells?

[FlameRetired]

I assume you mean the conditional formatting formulas?

Just use format painter. Format manager will update the ranges for you.

If you meant this formula

Please Login or Register  to view this content.

then just change the cell reference A$5 to A5. Then copy / paste into other cells.

[bigtmovers]

Flame,

I meant the autofill. How do i use that Autofill in other cells where it changes the colors.

[FlameRetired]

The cells are non contiguous. You can copy/paste the first cell or you can pre select all the cells (Ctrl + mouse click) then type the formula into the first cell. Then with just Ctrl pressed hit Enter. All the pre selected cells should fill with the formula.

Is that what you meant?

[bigtmovers]

Flame,

I'm trying to change the cell and the color but its not working. I have attached the file. It needs to be Cell C40 "Total Gross". So if Cell C40 "Total Gross" is not 1/3rd of Cell C44 is would change color.

[FlameRetired]

I don't understand. The "Total Gross" will never be 1/3 of "Total Net" "Total Gross" will always be >= "Total Net". It has to be the other way around.

What am I missing?

[JeteMc]

I feel as if bigtmovers is asking how to apply Dave's conditional formatting rules to C83, I83 and O83. If that is the case fill the 'Applies to' for both rules as follows: =$C$42,$I$42,$O$42,$U$42,$C$83,$I$83,$O$83
Her is a copy of the file with the 'Applies to' modified so that it should work in all seven of the 'Total Net' cells.
Let us know if you have any questions.

[bigtmovers]

Flame,

I got that figure out. I'm almost done and just need few more help with formulas.

• How to Hide Cells in Week 5 based on end of the month? I have already done the end of the month function and now I need help to hide the cells if the date doesn't show up.
• How to properly count Office Labor In Week 5 based on end of the month. As you currently see it says "Value"

I have uploaded update file and thank you for all the help.

[JohnTopley]

In C359

=IF(--(ISNUMBER(A$323)),SUMPRODUCT(--(WEEKDAY(A$323,2)<7),$U$327),0)