https://deu.proz.com/forum/office_applications/354400-excel_calculate_period_between_dates.html
This site uses cookies. Some of these cookies are essential to the operation of the site, while others help to improve your experience by providing insights into how the site is being used. For more information, please see the ProZ.com privacy policy.
Cookie settings

Technische Foren » Office applications »
Excel: calculate period between dates
Initiator des Themas: Samuel Murray
Samuel Murray
Samuel Murray  Identity Verified
Niederlande
Local time: 11:12
Mitglied (2006)
Englisch > Afrikaans
+ ...
Nov 17, 2021

Hello

Suppose I have two columns of dates & times in Excel, how can I calculate the time difference between the two columns?

So, cell A1 = "28/01/2011 17:57" and cell A2 = "01/07/2011 11:14". According to an online calculator, the time difference is 154 days 17 hours 17 minutes.

I don't care in what format the answer is given: days/hours/minutes, hours/minutes, hours, minutes, seconds, etc., as long as I can see the difference.

I tried convert... See more
Hello

Suppose I have two columns of dates & times in Excel, how can I calculate the time difference between the two columns?

So, cell A1 = "28/01/2011 17:57" and cell A2 = "01/07/2011 11:14". According to an online calculator, the time difference is 154 days 17 hours 17 minutes.

I don't care in what format the answer is given: days/hours/minutes, hours/minutes, hours, minutes, seconds, etc., as long as I can see the difference.

I tried converting the dates to computer time (you know, offset from 1970) but could not figure out how.

Thanks
Samuel ▲ Collapse


Mihai Badea (X)
 
Tomasz Sienicki
Tomasz Sienicki  Identity Verified
Dänemark
Local time: 11:12
Mitglied (2007)
Dänisch > Polnisch
+ ...
A simple subtraction Nov 17, 2021

=A2-A1

…works for me.

Thomas T. Frost
 
Michael Davies
Michael Davies  Identity Verified
Dänemark
Local time: 11:12
Mitglied (2009)
Dänisch > Englisch
+ ...
Excel: Time difference between 2 dates/times Nov 18, 2021

You can find 2 different methods for this calculation at the following URL:

https://support.microsoft.com/en-us/office/calculate-the-difference-between-two-times-e1c78778-749b-49a3-b13e-737715505ff6

Regards,

Michael
 
Daryo
Daryo
Vereinigtes Königreich
Local time: 10:12
Serbisch > Englisch
+ ...
Differences in resolution .... Nov 18, 2021

Samuel Murray wrote:

Hello

Suppose I have two columns of dates & times in Excel, how can I calculate the time difference between the two columns?

So, cell A1 = "28/01/2011 17:57" and cell A2 = "01/07/2011 11:14". According to an online calculator, the time difference is 154 days 17 hours 17 minutes.

I don't care in what format the answer is given: days/hours/minutes, hours/minutes, hours, minutes, seconds, etc., as long as I can see the difference.

I tried converting the dates to computer time (you know, offset from 1970) but could not figure out how.

Thanks
Samuel


converting time from an Excel cell to computer time might require some serious tinkering: the operating system counts time in milliseconds (or even with a 100ns resolution), Excel time is counted by whole seconds.

Even the simple "time difference between 2 Excel cells" is not always so "simple" - you have to be careful not to get results with a meaningless precision.
 
Platary (X)
Platary (X)
Local time: 11:12
Deutsch > Französisch
+ ...
Timestamp Nov 18, 2021

Samuel Murray wrote:

I tried converting the dates to computer time (you know, offset from 1970) but could not figure out how.



Have a look here : https://www.epochconverter.com/

In Excel (2016) it corresponds to = (Cellwithhumandate-DATE(1970;1;1))*86400

Could be different formulas, I use this one when needed.

Hope this helps!
 


To report site rules violations or get help, contact a site moderator:

Moderatoren dieses Forums
Maya Gorgoshidze[Call to this topic]
Prachya Mruetusatorn[Call to this topic]

You can also contact site staff by submitting a support request »

Excel: calculate period between dates


Forum rules
Help and orientation




Wordfast Pro
Translation Memory Software for Any Platform

Exclusive discount for ProZ.com users! Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value

Buy now! »
CafeTran Espresso
You've never met a CAT tool this clever!

Translate faster & easier, using a sophisticated CAT tool built by a translator / developer. Accept jobs from clients who use Trados, MemoQ, Wordfast & major CAT tools. Download and start using CafeTran Espresso -- for free

Buy now! »