# Concatenate Date+Time+Am or PM then sum hours

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

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

4. ## 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. ## Re: Concatenate Date+Time+Am or PM then sum hours

Thanks to you both. Working perfectly!!

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

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

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

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.06947 seconds
• Memory Usage 9,050KB
• Queries Executed 15 (?)
Template Usage (32):
• (1)footer
• (1)forumrules
• (1)gobutton
• (8)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (2)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 (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_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_attachment
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete
• forumrules