Solved

# Time Date Calculations

Posted on 2014-02-03
Medium Priority
493 Views
Good Morning,

Have both date time combined and separated in excel.  Need to calculate both the median on the combined date/time and subtraction on the separated date/time.  Not sure what formula/formats to use.

See attached file.

Thanks
Nick
time-date-example.xml
0
Question by:nmolliconi

LVL 4

Assisted Solution

gozoliet earned 1000 total points
ID: 39829746
To get an actual time from A/B3 use:
=A3+TIMEVALUE(CONCATENATE(LEFT(B3,LEN(B3)-2),":",RIGHT(B3,2)))

To get an actual time for C/D3 use:
=C3+TIMEVALUE(CONCATENATE(LEFT(D3,LEN(D3)-2),":",RIGHT(D3,2)))

I'm sure there's more elegant, but basically you are creating a time from the time field, keeping in mind that sometimes the horus are 1 digit, sometimes there's 2 digits, and then adding it to the date.

From there you can use the usual "Median" or substracting two dates formulas.
0

LVL 50

Accepted Solution

barry houdini earned 1000 total points
ID: 39829799
Hello Nick,

For your subtraction try this formula for row 3 copied down

=TEXT(D3,"00\:00")+C3-TEXT(B3,"00\:00")-A3

format as [h]:mm

and for the median do you just want a median of all those values? If so try a simple MEDIAN function like

=MEDIAN(A14:B18)

see highlighted cells on the attached

regards, barry
time-date-barry.xls
0

Author Closing Comment

ID: 39835695
Thank you both.  Each solution worked

Nick
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.