Time Date Calculations

Posted on 2014-02-03
Medium Priority
Last Modified: 2014-02-05
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.

Question by:nmolliconi

Assisted Solution

gozoliet earned 1000 total points
ID: 39829746
To get an actual time from A/B3 use:

To get an actual time for C/D3 use:

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


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


see highlighted cells on the attached

regards, barry

Author Closing Comment

ID: 39835695
Thank you both.  Each solution worked


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question