# Excel dates

1. ## Excel dates

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.  Register To Reply

2. ## Re: Excel dates

=if(month(a1)=month(b1),1,(b1-eomonth(b1,-1))/12)  Register To Reply

3. ## Re: Excel dates

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?  Register To Reply

4. ## Re: Excel dates

How do I combine all these formulas in one cell?

=IF(MONTH(D5)=MONTH(E5),1,(E5-EOMONTH(E5,-1))/12)
=IF(K9="en route",((L9-EOMONTH(L9,-1))/12))
=IF(L11="en route",(+EOMONTH(K11,0)-K11)/12)

Each work quite fine if separated, I just want to combine them because my two columns contain those three conditions.  Register To Reply

5. ## Re: Excel dates

It's difficult to tell without an example of your data. None of the conditions you've listed would preclude the others.

Possibly:
=IF(MONTH(D5)=MONTH(E5),1,IF(K9="en route",((L9-EOMONTH(L9,-1))/12),EOMONTH(K11,0)-K11)/12))  Register To Reply

6. ## Re: Excel dates

See the attached excel sample. I want to combine second and third formula in the first formula. so that it works throughout the whole column.  Register To Reply

7. ## Re: Excel dates

In D5, try,
Formula:  `Please Login or Register  to view this content.`
and copy down. Is this what you need ?  Register To Reply

8. ## Re: Excel dates

Wow, that worked. Thanks a lot.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 