Calculate Hours between Dates and Times in Excel | Excel forum

Resolved · Medium Priority · Version 2010

Mon 20 Apr 2015, 14:43 Delegate Adrian said...

Adrian has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Intermediate course

Calculate Hours between Dates and Times in Excel

Hi

Could someoen please advise how I could extract the number of hours between the following:

StartDate StartTime EndDate EndTime
01-Apr-15 1300 01-Apr-15 600
01-Jul-15 600 30-Sep-15 600
01-Jul-15 600 30-Sep-15 600
01-Jul-15 600 30-Sep-15 600
01-Sep-15 600 30-Sep-15 600
01-Jul-15 600 30-Sep-15 600
03-Apr-15 600 30-Apr-15 600
03-Apr-15 600 30-Apr-15 600
01-Jul-15 600 30-Sep-15 600
03-Apr-15 600 30-Apr-15 600
01-Apr-15 2000 01-Apr-15 600

I have tried =((C2+D2)-(A2+B2))*24 formula, but that brings out a crazy number, even if the times are 13:00. I have tried formatting but still doesn't work.

Many thanks

Mon 20 Apr 2015, 20:54 Trainer Jens said...

RE: Calculate Hours between Dates and Times in Excel

Hi Adrian,

Thank you for the forum question.

I are using the right formula but I cannot explain why you do not get the right result.

I have attached a workbook where I am using the same formula and get the right result. It must be a formatting problem.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

time Calc.xlsx

Tue 21 Apr 2015, 09:34 Delegate Adrian said...

RE: Calculate Hours between Dates and Times in Excel

Hi Jens

Thanks for your help - I will look at the formatting in more details and see where its going wrong.

Kind regards

Adrian

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Using Alt in Save Dialog Box

When you are saving (or opening) a file, try these;
(Note the numbers are on the keyboard not the numeric keypad)
1. Go to previous folder Alt+1
2. Go up one folder level Alt+2
3. Search the Web Alt+3
4. Delete selected file Alt+4 or Delete
5. Create a new folder Alt+5
6. Cycle through all views Alt+6 repeatedly
7. Display the Tools menu Alt+7

View all Excel hints and tips