Hi all,
I'm trying to find out the difference between 2 times and dates that are in the format: dd/mm/yyyy hh:mm:ss and both in a 24 hour format. What would the best way to go about this be? The sort of result I'm looking for would be like the below example:
12/04/2017 14:52:29 MINUS 12/04/2017 14:51:21
With the result being how many minutes, hours and days difference, such as: 0 Days, 0 hours, 1 minute and 8 seconds.
Any advice is much appreciated
Write your equation as you have there:
=sum(Time1-Time2)
Just change the format of the answer to hh:mm:ss
Home>Number>Custom
This has helped a lot, thanks! The only issue is where there's a difference in the day as well. Let's say:Originally Posted by lcartwright
13/04/2017 14:52:29 MINUS 12/04/2017 14:51:21.
How would I allow for this?
On that topic as well, let's say the 13th was a public holiday and I wanted to remove the 24 hours of that public holiday from the equation. Is there a way I can work in the "networkdays" formula somehow so that public holidays and standard weekends are filtered out?
use this custom
Formula:
Please Login or Register to view this content.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Specifically, this will be the custom format string (More Number Formats ... | Custom):
d" days," h" hours," m" minutes and" s" seconds."
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
To filter out holidays:
=NETWORKDAYS(A2,A1) or =NETWORKDAYS.INTL(A2,A1)
If you need to specify holiday date, add the range (e.g. C1:C5):
=NETWORKDAYS(A2,A1,C1:C5) or =NETWORKDAYS.INTL(A2,A1,C1:C5)
ps networkdays strips the time information
so only returns days
you need to capture the time difference separately
assuming C2:c3 is your public holidays
Formula:Please Login or Register to view this content.
Thank you humdingaling and AliGW. Between your comments, and lcartwright's earlier comment, I've been able to get everything working perfectly (with public holidays getting counted as expected). Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks