merging columns - one date, one time

B

batfish

I am trying to merge two columns - one with the date and another with the
time so that eventually I can subtract two merged dated/time columns from
each other. (there are multiple date and time columns to be merged for
various time elements being looked at) I tried conconate but that gave me a
jibberish number. The problem I am having is that if the event time frame
goes past midnight, using just the time column, the answer is a negative
number and thus invalid.
 
P

Peo Sjoblom

Just add them if they are numeric dates and times, a day in Excel is 1 and
an hour is 1/24, so if you have

01/01/05 in A1 and 10:00 in B1

= A1+B1

formatted as mm/dd/yy hh:mm

will return 01/01/05 10:00

--
Regards,

Peo Sjoblom

(No private emails please)
 
R

Ron Rosenfeld

I am trying to merge two columns - one with the date and another with the
time so that eventually I can subtract two merged dated/time columns from
each other. (there are multiple date and time columns to be merged for
various time elements being looked at) I tried conconate but that gave me a
jibberish number. The problem I am having is that if the event time frame
goes past midnight, using just the time column, the answer is a negative
number and thus invalid.
Click to expand...

An alternative, if your event time frames never exceed 24 hours, would be to
use this formula:

=EndTime-StartTime +(StartTime>EndTime)

Format the results as [h]:mm






--ron
 
Top