# 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.

2. ## Re: Excel dates

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

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?

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.

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))

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.

7. ## Re: Excel dates

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

8. ## Re: Excel dates

Wow, that worked. Thanks a lot.

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.08624 seconds
• Memory Usage 9,060KB
• Queries Executed 16 (?)
Template Usage (34):
• (1)footer
• (1)forumrules
• (1)gobutton
• (8)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (8)postbit_legacy
• (8)postbit_onlinestatus
• (8)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (50):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• postbit_attachment
• tag_fetchbit
• tag_fetchbit_complete