LinkBack URL
About LinkBacks
Hi everyone,
Pls. i have already a formula that i need to be corrected. The value i needed is showing in the next month.
I have a start date in nov-27-2020 but the cost is reflected in Dec-2020.
I attached the file for easy reference.
Thanks
Ricky
Hi everyone,
Pls. i have already a formula that i need to be corrected. The value i needed is showing in the next month.
I have a start date in nov-27-2020 but the cost is reflected in Dec-2020.
I attached the file for easy reference.
Thanks
Ricky
Last edited by rickyilas; 12-09-2022 at 03:15 AM.
Hi,
Try to explain to us what exactly you want to calculate with that formula, what are the conditions and why it should give the expected result. It is time-consuming to try to analyze a formula when there may be other alternatives to solve the problem
Diana Tanase
If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
Try:
=IF(JS$4<EOMONTH($K5,-1)+1,"",IF(EOMONTH($K5,-1)+1=JS$4,$AA5*(JS$3-$K5+1)/30.5,IF(EOMONTH($L5,-1)+1=JS4,$AA5*($L5-JS$4+1)/30.5,IF(EOMONTH($L$5,-1)+1>JS$4,$AA5,""))))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glenn,
Thank you for your solution. It's exactly what i want. I really appreciate your help.
Cheers!
Just check the value returned at the date of the last month... My answer differs slightly from your monster formula. If you believe yours is correct, explain why.
I suspect it may be down to the fact that you have considered that every month has 30.5 days....
Yours is ok, i actually wanted the exact number of days each month instead of 30.5 but don't know how to formulate it without using the 30.5 inside the formula. is it possible to still change the formula :
=IF(JT$4<EOMONTH($K5,-1)+1,"",IF(EOMONTH($K5,-1)+1=JT$4,$AA5*(JT$3-$K5+1)/30.5,IF(EOMONTH($L5,-1)+1=JT$4,$AB5*($L5-JT$4+1)/30.5,IF(EOMONTH($L5,-1)+1>JT$4,$AB5,""))))
Thanks
OK. I'll fix that a bit later today. I have to go do the shopping now!!
OK. Now it calculates actual days pm.
IF(JS$4<EOMONTH($K5,-1)+1,"",IF(EOMONTH($K5,-1)+1=JS$4,$AA5*(JS$3-$K5+1)/(JS$3-JS$4+1),IF(EOMONTH($L5,-1)+1=JS4,$AA5*($L5-JS$4+1)/(JS$3-JS$4+1),IF(EOMONTH($L$5,-1)+1>JS$4,$AA5,""))))
Hi Glenn, Thanks for all ur help. This is perfect now for my work. My colleagues will also like this. Thanks a lot!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks