Hi
Let's say i have a Monthly report to submit on or before 1st of every month to 5th of every month, then i want to have a formula where automatically when i key the date, it will allot 20 points. If the report is submitted from 6th of the month to 9th of the month, it should be 10 points. If submitted after 10th of every month it should be 0 points. What is the formula. Please can you assist me. Thanks and regards Kristo
Hello Kristo and Welcome the Forum!
If you could provide us with a spreadsheet example of your data, someone will be able to help you. Also, when state "..before the 1st of the month" is it a day before or more?
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hi and welcome to the forum
If you have your date in A1, use this...
=IF(A1<TODAY()+5,20,IF(A1<TODAY()+9,10,0))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Enter Date in A1:
=IF(AND(DAY(A1)>=1,DAY(A1)<=5),20&" pts",IF(AND(DAY(A1)>5,DAY(A1)<10),10&" pts",0&" pts"))
PLs can you give me solution for columns in blue. Thanks Kristo
Not sure what you asking for. What columns in blue?
Hi
Thanks for your kind help. It's like this.
Pls note the following criteria
If report submitted on or before due date = 20 points
If report submitted 1-4 days after due date = 10 points
If report submitted 5 days after due date = 0 points
So now is the example
Report x has to be submitted on 15th of Aug. But it has been submitted only on 18th of Aug
Report y has to be submitted on 28th of Aug. But it has been submitted only on 4th of Sep
Report Z has to be submitted on 1st Aug of every year. But it has been submitted only on 10th Aug
Now please tell me the formula for allocating points based on above criteria in Aug automatically when i key in the report submitted dates.
Will be grateful for your kind assistance.
Thanks Kristo
did you try the suggested formulas yet?
If you tried to upload a file, we didnt get itPLs can you give me solution for columns in blue. Thanks Kristo
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Hi
Thank you so much for your kind response
Pls find attached the file wherein I would request you to please fill the formula in the yellow shaded column based on the criteria mentioned earlier.
Will be grateful
Thanks and regards
Kristo
Try this one:
=IF(E7-C7<=0,20,IF(AND(E7-C7>0,E7-C7<5),10,0))
Also, there is a problem with how you define your criteria. "...If report submitted on or before due date = 20 points" You did not specify what before means. Is it a day before, two, five? I think you should confine your logic to the days difference for each criteria.
Hi AlKey
Thanks again for your response. See that formula you gave and the earlier one are ok as far as the dates are input till 20th August for ex. ok. Pls. note this I have clearly mentioned in my excel sheet (attached now)the criteria just see that before due date, 4 days after due date and 5 days or more after due date.
In this case, for ex. your formula does not work correctly let's say. 28th of August is the due date. If the report is submitted on 28th or before it is 20 points. If it is submitted 4 days later i.e. 1st September, now tell me how will that formula work. Also, if it submitted 5 days later i.e. 2nd Sept or later tell me how your formula will work. It does not work. Hence, pls. could you assist me.
Thanks and regards
Kristo
Ok, Here is what I suggest we do. Use the last formula that I provided. Enter it in the your template cell I7 and drag it down.
Please identify any discrepancy in the calculation and give me your explanation.
=IF(E7-C7<=0,20,IF(AND(E7-C7>0,E7-C7<5),10,0))
Best regards,
Hi AlKey
Wow. It's perfect now. Thanks a lot my friend. Your very prompt and sincere response and attitude are highly commendable.
Hats off to you. Anyways, what's your name and is this a free service or how does this forum work. What's your role. Pls. let me know if there is anything i can do to reciprocate my thanks.
Thanks and regards
Kristo
Thank you. I am glad I could help. Everybody here donate their time and knowledge to help others. Please don't forget click on Add Reputation * for those who helped.
Hi
Thanks again. Where is "Add Reputation". unable to identify. Pls. assist. Thanks, Kristo
Hi FDibbins
Thanks for your time and prompt response. Really appreciate your support. I have found the solution through AlKey as I was trying to take your help also.
Thanks and regards
Kristo
It is right under my name
Im glad you got the answer you wanted. I could see that Alkey was giving you what you wanted - which they did REALLY well, so I backed off to avoid confusion
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks