splitting text and calculating time

[Kajuliano]

Hi--

I'm new to excel and here is my issue:

I have two columns of data, the first column has both the date
(year-month-date) and time (in hours, minutes, and seconds-military) that a
participant started my questionnaire. The second column has the same exact
information (both date and time) but this tells me when the participant ended
the survey. This information lets me know how long it took an indiviudual to
complete the questionnaire and on what day they started it.

I need to:

1. split the date and time into two different columns (for both column 1 and
2- giving me 4 columns total, 2 with date, 2 with time)
2. calculate the time it took the participant to complete the survey
(subtract the end time from the start time)

I really have no idea where to begin, so any help would be appreciated-
thanks!

 

[JE McGimpsey]

One way:

A1: <start date-time>
B1: <end date-time>

Split Date & time (format as date or time as appropriate):

C1: =INT(A1)
D1: =MOD(A1,1)

Difference:

G: =B1-A1

Format this last using Format/Cells/Number/Custom [h]:mm:ss. The
brackets prevent roll-over at 24 hours.

 

[Mike H]

Hi,

All you actually need to do is format each cell differentll so

A1= 01/01/2008 01:26:22
b1= 01/01/2008 01:38:42

in C1 enter =a1 and format as dd:mm:yy and you get the date
in D1 enter = a1 and format as hh:mm:ss and you get a1 time
in E1 enter =b1 and format as hh:mm:ss and you get b1 time
in F1 ente = E1-D1 and you get the test duration

Mike

 

[Sean Timmons]

Not sure if you wanted to split the columns for the purposes of calculation
or because you need to do it anyway.

To calculate the difference, you need only do end time minus start time.
Format as mm:ss assuming it takes minutes to complete.. or hh:mm:ss if you
need hours.

If you do want to split the column, you can do a round for the date...

add 2 columns.. in the new column B, =rounddown(A2,0) gives the date without
time.
in the new column C, enter A2-B2. This gives time without date.

Make sure to format your column B as date and column C as time with hh:mm:ss
or hh:mm

Do the same for end time.

 

[Mike H]

Hi,

I should mention I have assumed the questionairre starts and finishes on the
same day.

Mike

 

[David Biddulph]

Does he need to format each cell differently?

What's wrong with
2008-01-01 01:26:22
2008-01-01 01:38:42
if that's what the OP prefers? (and remember that this would be the ISO8601
date format)

And =B1-A1 would give the duration.

 

[Kajuliano]

Thanks for all the relies but I am still having troulbe (I know very little
about excel). How do you format the column as dd:mm:yy?

 

[Kajuliano]

I'm not sure what it means to format the cells differently, but I do have
different dates and all the times are different as well

 

[Mike H]

Hi,

To format a cell with the cell selected

Format|Cells - select number tab - Custom

enter the formats in my previous post

Mike