+ Reply to Thread
Results 1 to 8 of 8

Excel dates

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    Dar es salaam Tanzania
    MS-Off Ver
    2019
    Posts
    48

    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. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Excel dates

    =if(month(a1)=month(b1),1,(b1-eomonth(b1,-1))/12)
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    06-26-2014
    Location
    Dar es salaam Tanzania
    MS-Off Ver
    2019
    Posts
    48

    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. #4
    Registered User
    Join Date
    06-26-2014
    Location
    Dar es salaam Tanzania
    MS-Off Ver
    2019
    Posts
    48

    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. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    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. #6
    Registered User
    Join Date
    06-26-2014
    Location
    Dar es salaam Tanzania
    MS-Off Ver
    2019
    Posts
    48

    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.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Excel dates

    In D5, try,
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and copy down. Is this what you need ?
    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.

    Regards,
    Sarang

  8. #8
    Registered User
    Join Date
    06-26-2014
    Location
    Dar es salaam Tanzania
    MS-Off Ver
    2019
    Posts
    48

    Re: Excel dates

    Wow, that worked. Thanks a lot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] excel formula to help my calculation from min dates of 3 dates cell ranges
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-04-2013, 10:32 AM
  2. Excel Dates copied from CSV file dont show as Dates
    By vew32 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-27-2013, 09:30 AM
  3. Replies: 10
    Last Post: 09-04-2013, 08:34 PM
  4. Recognizing European Dates as Dates in Excel 2007
    By sdekker22 in forum Excel General
    Replies: 1
    Last Post: 10-25-2009, 05:07 PM
  5. Replies: 2
    Last Post: 04-14-2009, 06:43 AM

Tags for this Thread

Bookmarks

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 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (8)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (8)postbit_legacy
  • (8)postbit_onlinestatus
  • (8)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
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
  • ./showthread.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_facebook.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_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • 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
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1