I’ve got an excel table with dates trucks left and returned, first column is date went, second column is date returned, third column should be number of trips the truck has made.
If the truck left 26th May and hasn’t returned till the end of month, I want the third column to give an answer of, ((31-26)/12). {31 being number of days of the May month, 26 being the date the truck left, 12 is just a factor}
Again if the truck returned in 6th June but didn’t leave in June, I want the third column to give an answer of (6/12) {6 being days spent in June}
Again if the truck left and returned within the same month, regardless of the number of days spent, I want the third column to give an answer of 1{1 being one trip}
And if the truck left in 24th May and retuned 8th June, I want the third column to give an answer of (8/12) 8 being days of June, 12 is just a factor.
Wow, that worked like a miracle. Thanks a lot.
I forgot to mention a thing in the formula. If a truck hasn't returned till the end of month, I put the word "en route" in the second column, meaning the truck is still on route. what should I now add to the formula you gave me?
If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.
If your requirement has been solved please mark your thread as Solved.
In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".
Kindly use [FORMULA] or [CODE] tags when posting your code.
Bookmarks