Counting the number of hours between two date/time fields

K

Kommerce

New Member
Joined
Jun 25, 2014
Messages
27
As the title says, I'm basically looking to calculate the difference in hours between two date/time fields in this format DD/MM/YY HH:MM.
I have tried using a custom add in I found but it doesn't seem to work.
Is there a potential VBA/macro solution?
Any help would be greatly appreciated.
Thanks in advance!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
You can use a simple formula

Excel Workbook
ABC
101/11/2014 09:4502/11/2014 14:0528
Sheet1
 
Upvote 0
MARK858 said:
You can use a simple formula

Sheet1

*ABC
101/11/2014 09:4502/11/2014 14:0528

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:111px;"><col style="width:144px;"><col style="width:117px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
C1=INT((B1-A1)*24)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

Thanks for your help! Is there a way however to make the final value non integer i.e. in hours and minutes?

Thoughts?
 
Upvote 0
Use the formula =B1-A1
and then apply the custom format "hh:mm" to the cell.
 
Upvote 0
If I was to add another layer of complexity i.e. counting the hours in between two dates and subtracting the hours that are not business hours i.e. 8-6 Monday-Friday, any thoughts on this challenge?

To clarify, I want to be counting the hours between the two dates that ARE business hours.
 
Upvote 0
Also using B1-A1 doesn't take into account the date difference, it only takes into account the time so if it was 2 days apart, it would still only provide me with the time difference (not taking into account the day difference)
 
Upvote 0
Thanks for your help! Is there a way however to make the final value non integer i.e. in hours and minutes?
Click to expand...


You will need to use the custom format [hh]:mm to the solution Michael has given you in post #4 as the hours go past 24 but you did ask for it in hours originally. Please try to ask questions more accurately.

I'm basically looking to calculate the difference in hours between two date/time fields
Click to expand...

Edit: ignore the solution above as didn't see the post where the OP has now added business hours. Time for me to get ready for work and so if anyone wants to jump in feel free.
 
Last edited:
Upvote 0
OOOps sorry.......format should be

[h]:mm
 
Upvote 0
MARK858 said:
You will need to use the custom format [hh]:mm to the solution Michael has given you in post #4 as the hours go past 24 but you did ask for it in hours originally. Please try to ask questions more accurately.
Click to expand...


Apologies for the confusion.

To re-clarify, i'm looking to get the difference of time between two dates and time in the format of HH:MM. So for e.g. 01/01/2001 00:00 and 03/01/2001 00:00 should return me 48.

To add a further level of complexity I'm looking for a way to take into account business hours as well as I have outlined in my aforementioned post.

Thanks again guys, you really are helping me a lot.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Time on X axis, and Dates on Y axis
Replies
1
Views
47
rlv01
R
WildBurrow
  • Solved
Date Validation Error
Replies
8
Views
85
WildBurrow
WildBurrow
D
  • Solved
Date time Format
Replies
3
Views
183
awoohaw
awoohaw
T
  • Question
Calculate only number of hours between two date & time
Replies
5
Views
496
SanjayGMusafir
SanjayGMusafir
D
  • Solved
Highlighting cells with date/time greater than 29 minutes past the hour
Replies
6
Views
174
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,649
Messages
6,120,730
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top