+ Reply to Thread
Results 1 to 8 of 8

Concatenate Date+Time+Am or PM then sum hours

  1. #1
    Registered User
    Join Date
    06-14-2013
    Location
    WA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Concatenate Date+Time+Am or PM then sum hours

    Have six columns: "Date in" "Time In" "AM or PM" "Date Out" "Time Out" "AM or PM"

    What I'm trying to get to from these columns is the amount of hours passed from time in to time out. It could be anywhere from a few hours to weeks.

    I keep getting the dreaded #value error when I take the difference of my concatenated data(and I'm probably doing that wrong also).

    Thanks all!
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Concatenate Date+Time+Am or PM then sum hours

    hi bjsmithinc, welcome to the forum. try this in D5:
    =A5+(B5/100&" "&C5)+0

    format accordingly to what you require. copy it to H5. then I5 is a simple:
    =H7-D7
    if you need to just see hours, custom format as:
    [h]
    with minutes:
    [h]:mm

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Concatenate Date+Time+Am or PM then sum hours

    no good just dividing b5 by 100 ,i know its 800 formatted as ??:?? dividing by 100 gives 8 fine for whole hours but if it was 08:30 then you'd get 8.3 hours which is not the same as 8.5 hours ie 30 mins =0.5 hours
    so i suggest
    d5 =IF(C5="PM",A5+VALUE(TEXT(B5,"00"":""00"))+0.5,A5+VALUE(TEXT(B5,"00"":""00")))
    h5==IF(G5="PM",E5+VALUE(TEXT(F5,"00"":""00"))+0.5,E5+VALUE(TEXT(F5,"00"":""00")))
    then i5 =H5-D5 formatte [hh]
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Concatenate Date+Time+Am or PM then sum hours

    thanks for pointing that out, martin.

    try this alternative too, modified using martin's formula:
    =IF(C5="PM",A5+TEXT(B5,"00\:00")+0.5,A5+TEXT(B5,"00\:00"))

  5. #5
    Registered User
    Join Date
    06-14-2013
    Location
    WA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Concatenate Date+Time+Am or PM then sum hours

    Thanks to you both. Working perfectly!!

  6. #6
    Registered User
    Join Date
    06-14-2013
    Location
    WA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Concatenate Date+Time+Am or PM then sum hours

    Thought things were working, but as I was trying various times I noticed some problems. There seems to be an issue when midnight is selected (e.g. 12:00 A.M.)
    This workbook will see a lot of midnight so I need it to work correctly.
    Thanks again for all help!!
    Attached Files Attached Files
    Last edited by bjsmithinc; 06-16-2013 at 01:58 PM.

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Concatenate Date+Time+Am or PM then sum hours

    if midnight is 12 am
    =IF(C5="PM",A5+TEXT(B5,"00\:00"),A5+TEXT(B5,"00\:00")+0.5)
    or better still use 24 hour clock
    nope thats wrong i just realise it will mess everything up!
    Last edited by martindwilson; 06-16-2013 at 05:25 PM.

  8. #8
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Concatenate Date+Time+Am or PM then sum hours

    maybe:
    =A5+(TEXT(B5,"00\:00")&" "&C5)

    please mark this as "solved" if it's ok.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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.06947 seconds
  • Memory Usage 9,050KB
  • Queries Executed 15 (?)
More Information
Template Usage (32):
  • (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
  • (2)postbit_attachment
  • (8)postbit_legacy
  • (8)postbit_onlinestatus
  • (8)postbit_wrapper
  • (4)showthread_bookmarksite
  • (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 (47):
  • 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_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1