Hey all!
Could really use some excel wizkid's help on this one... I am (evidently) not an expert in excel!
You'll find the spreadsheet attached with notes in specific boxes.
Whoever can help me with this, I sincerely thank you so much!
Should be relatively straight forward for someone who is savvy with excel. I'm not being lazy by not explaining the issue, it's just MUCH easier to see the spreadsheet than me write it out. Hopefully it's intuitive enough!
Last edited by domgilberto; 05-13-2014 at 05:28 PM. Reason: At the request of the moderator.
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.
Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Anyone help me correct this for conditional formatting? I want the line to be red on dates where the report was received back GREATER than 1 day where there is a column with "Commercial" (see spreadsheet in "Comms/Heating" column) written in there.
Please Login or Register to view this content.
If it has "Heating" in there, they have 2 days. So if we receive their report back 3 days later, again, this would be red?
All doneOriginally Posted by alansidman
Sorry about that!
select A5 to H33 or your actual data go to conditional formatting copy paste below
=OR(AND($C25-$B25>=1,$D25="Commercial"),AND($C25-$B25>2,$D25="Heating"))
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Hmmm. Doesn't seem to be working? Screenshot: http://screencast.com/t/yVuPH2hHL7
Likewise, can you see Column "I"? If the two dates dictate that its "RED" condition, can I have a text in the corresponding row in Column "I" that either says "Yes" or "No" if they're entitled to bonus? No idea how to do that!
THANK YOU SO MUCH for your input!
you can try
=iferror(if(OR(AND($C25-$B25>=1,$D25="Commercial"),AND($C25-$B25>2,$D25="Heating")),"NO',Yes"),"")
where ever you have pending formula will output empty cell.
Hemesh thank you - that works wonderfully.
I am so sorry, but do you know how to change that formula to IGNORE Saturday's and Sunday's? Likewise, is it possible (not to get too deep) but to factor in bank holidays? Sorry if I am asking too much!
EDIT: ALSO, how do I remove the "Yes" answer on rows that have no input? See this screenshot: http://screencast.com/t/HEQcPC5Ta0vV
Last edited by domgilberto; 05-08-2014 at 03:45 PM.
Try Below
Formula:
Please Login or Register to view this content.
Wow you beast! That is one epic formula! Thank you.Try Below
Formula:Please Login or Register to view this content.
It doesn't seem to be working properly though? Please see this screenshot I have attached to see whats happeneing with more clarity
try for Yes and No's
Formula:Please Login or Register to view this content.
for Conditional formatting
Formula:Please Login or Register to view this content.
Last edited by hemesh; 05-09-2014 at 05:36 AM.
Wow - that is incredible. So thankful for your help!
Feel free to jump in on my other thread - you seem like you know alot about excel (although I don't know if what I am asking can be done?)
Thank you so much! http://www.excelforum.com/excel-gene...-employee.html
You are welcome ! if this solves your query then mark thread as solved.
If you wish,You can click Add rep icon if my assistance has helped you.
Last edited by hemesh; 05-09-2014 at 09:18 AM.
This might be of help to you. I have incorporated your previous post with this one that includes a calculation for determining the right to bonus using workdays and have applied Conditional Formatting to the result highlighting the No's with red.
Last edited by newdoverman; 05-13-2014 at 05:38 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I am so thankful for everyone's input and help!
You sir (newdoverman) are a genius! That spread-sheet is literally precisely what I was after! Thank you so very much!
Love this forum, very very helpful!
Sorry, just wanted to ask one last question, is it possible to factor in "Bank Holidays" as a weekend for the Yes and No statements? If it's a case of using a list table to specify what these bank holiday days are for, so the next 3-4 years, then that would be no issue?
M18:M100 is highlighted in yellow. In this area you can enter in the holidays that you want to be considered in the formula. The range is already in the formula and when you fill in the holidays the adjustments will be automatic. There should be enough room to add the holidays. As the holidays pass and are no longer needed, delete them from the list and add new dates as needed.
Last edited by newdoverman; 05-13-2014 at 05:36 PM.
Not entirely sure if I am understanding you correctly? Could you kindly elaborate
Ok I get how it works now - I was being stupid! Very smart formula
That works perfectly, albeit 2 minor things(stupidly my fault!):
1) If the "Attended site" date is "Sat - 03/05/14" and the "Report received" is "Wed - 07/05/14", when I go to put in the corresponding holiday box "Mon - 05/05/14", this does not flag up as "Yes" for "Bonus Owed", instead its a No. This is because I stupidly forgot to say that if an Engineer attends site on either Saturday or Sunday, irrelevant if its commercial or heating, they are given 2 (business) days to get the report in.
2) Is is possible to have the "Bonus Owed" as blank instead of "No" if there is nothing corresponding to it on that specific row?
MASSIVELY appreciate your help!!
I think that I finally got both additional conditions met.
Here is the latest workbook with all of the changes incorporated to date.
Last edited by newdoverman; 05-13-2014 at 05:35 PM.
Thank you for a speedy response
If I put "Attended site" as " Sun - 04/05/2014" and "Report received" as "Tue - 06/05/14", this will be flagged as a "No" for bonus owed. Is it possible to change the formula so that if an engineer attends site on either Saturday or Sunday, irrelevant if its commercial or heating, they will get 2 days?
Massive thanks for your time!!
I think that I have added what you want without messing up the rest...LOL
Last edited by newdoverman; 05-13-2014 at 05:33 PM.
Seriously... That's incredible! Wish I had your knowledge like that in Excel... Actually really impressed! Hopefully one day I'll be able to write formula's like that... still quite a novice in excel.
You've literally nailed it! I can't thank you enough!
AHHHHH!
Slight issue! If "Report received" box is blank, it is saying the engineer is owed a bonus???
How do I change that formula so that if the "Report received" box is blank, it will say "No" always under this circumstance??
Anyone know how I can change this so that if D15 has nothing in there, the results are always "No", however, if there is a date in there, it needs to run through the formula to see if it should be "Yes" or "No"...????Please Login or Register to view this content.
Change
=IF(B15<>"", to =IF(D15<>"",
It seems that your "real" workbook has a different setup than the workbook that I have been working on seeing that the cell references are different. If there are further problems, it might be a good idea to post what you are actually working with.
I may be able to shorten the formula a bit when you are satisfied that it is working for you the way that you want. I hesitate to send the revision that I have in mind without seeing exactly what is different between the workbooks.
Last edited by newdoverman; 05-11-2014 at 02:21 PM.
Bingo! Thanks again!
Now this is definitely solved! Haha, thanks for your help and patience :P
Thank you for the feedback.
Did you by chance insert a column on the left-hand side of the chart starting in row 18? I think that I can improve the formula but would want to be certain of what I would be dealing with.
Not sure if I understand what you're asking me? I don't think I added anything. I changed a few things ever so slightly, but other than that, nothing dramatic? Why, what are you thinking?
I was thinking that I MIGHT be able to shorten the formula but having taken another look at what I uploaded, the effort may not be worth it.
You've done a superb job! Nothing to shorten
Thanks very much!
You're welcome.
Thank you for the feedback
Have spoken with newdoverman and came to the conclusion it may be best to continue this thread as opposed to start a new one (especially when its the same spreadsheet)! So switched to 'unresolved' again, although, I think we all know the original solution has been solved
Either way:
If I wanted to conditional format any rows where-by if the "Received Date" has been left blank for the same length of time that they have NOT qualified for the bonus (i.e. "No" to Bonus Owed), then it would turn the row red? To elaborate that further, I want to see how many engineers DO NOT return their engineer in the predefined time given, but not give a "Yes" or "No", but instead just flag up that row that it is overdue using the same rules applied to the "Yes" and "No"?
I assume this would need to use the local computer date (MM/DD/YY)?
With the changes that have been made, just to be sure that we are working on the same worksheet, could you upload the absolute latest version of the file so that any solution offered will be appropriate to what you are working with.
If I understand properly, you want to add a conditional formatting to show where received is still blank and the Bonus says No.
I have read back some of the messages and I think that the ranges have changed somewhat because the cell references in messages 25 and 26 point at empty cells on the latest workbook that I posted....just want to be sure that answers will be correct with what you are working with
Last edited by newdoverman; 05-13-2014 at 06:03 PM.
Looking at my last upload, if this is what you are working with, has a flaw (I think) in the formula that we didn't anticipate. That is, if The received date isn't filled in, the bonus reads "Yes"...it should read "No" because how can you be eligible for a bonus when the paperwork hasn't been completed.
I have just been messing around with my last upload to re-familiarize myself with the file and changed the formula in the Yes/No column so that if the paperwork hasn't been submitted, the bonus will say No and there is a flag in column J to indicate that the report hasn't been received.
If the Report hasn't been received, the row will go red until the report has been received. If on time, the red will disappear and the word Yes under Bonus will appear. If no report is received, the "flame" will be in the next column from Yes/No.
Is this going in the direction that you envisioned?
Looks sweet. How about changing the "Report not received" to "Report Overdue" and having a Yes in there populate automatically if the report is overdue (based on the pre-existing rules).
Likewise, if there is no date in "Report received" column, I'd rather "Bonus Owed" be blank. That way when I do reporting I can see what is overdue and what is still pending within the allowed timeframe? On top of that, I can see if there are a tonne of "No"s, which means we have a report in, it was overdue, but we got it for nothing as they didnt get it in on time! So it helps for reporting.
Likewise, if the report is handed in on the same date as "Site Attended", this would obviously flag up as "Yes" too, right?
Thank you
Last edited by domgilberto; 05-14-2014 at 09:10 AM.
Report Not Received is now Report Overdue. Contents of this column will indicate YES if report is overdue (and not received). The Report Received will remain uncoloured and empty until it is filled in. If the Report Received is on time the red will disappear from the row and the bonus will indicate YES. Once the over due report is received, the overdue is removed but the row remains red.
If the report is received on the same day as the Attended Site, the bonus will indicate YES.
Looking good! Tell me if I am asking too much of you now
- Neither Yes or No will appear in "Bonus Owed" UNLESS there is a date in "Report Received" (just blank).
- If Report is Overdue, have a "Yes" in the Report Overdue column (forgive me, but I think you've done this but it's not working?)
- Highlight the row Red, if it is overdue, i.e. Yes in Report Overdue column?
That's pretty much it, other than that, what a fantastic help you've been!
I think that I have addressed the latest concerns. Make sure that you have column K inserted with the formula so that the DUE DATE is calculated. That makes the OVER DUE column work. This column can be hidden. The Bonus Owed column will not indicate YES or NO until a date is entered in REPORT RECEIVED. I extended the "FIRE" if the report hasn't been received and is overdue to include the whole row except the cell for REPORT RECEIVED.
It works perfectly so far here as per your requirements as far as I can tell. If you are making changes in another workbook to match these edits, be very careful that the references are correct because a small mistake will stop a lot from working.
So impressed! Would it be stupid to ask whether or not you learned excel on a course, or was it down to trial and error and learning through forums like this?
It's quite remarkable! I will be sure to study the formula's you've made so that hopefully one day I can apply the same principles to future spread-sheets.
From taking a glance at what you've done, it looks literally perfect! Absolutely brilliant!
I will take a closer look tomorrow. It's relatively late across the pond here
I'll report back tomorrow!
Checked it over. Perfect! Flawless. Thank you!
I need a little few more fields on there, but I don't want to to take the micky and ask too much! So I will be creating new threads and asking specific question to help learn them too
Thank you! (feel free to drop in on the new threads and give your input, would be really helpful, after-all, the questions I am asking work in conjunction with what you have made - indirectly at least).
Thank you for the feedback. I'm glad that you are happy with it.
When adding fields, do it one field at a time and test that nothing has "broken" in the process. Then save it before going on to the next phase. It would be handy to keep a log of what you did in each step so that you can easily do trouble shooting as the process evolves.
I will look out for your posts as much as I can.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks