Calculating hours and Days

[MattyMarco]

Hi,
Im currently working for my dad (car hire company) and he needs me to make a spreadsheet that will calculate the number or hours between days.
E.g.
Because if someone collects a car at 12pm on 3/8/11 and brings it back before 12pm on the 4/8/11 the 24hr period is 1 day but if its brought back say 1pm then its 25 hours and it needs to show as 2 days.

any guidance would be a grat help, thanks!!

 

[bertie]

Hi and welcome to the forum.

Here is one way to do it. To keep the formula relatively simple I have used this layout.

Excel Workbook
	A	B	C	D
1	Date Out	Date In	Hours Diff	#Days
2	03/08/2011 12:00 PM	04/08/2011 11:00 AM	23	0.96
3	03/08/2011 12:00 PM	04/08/2011 01:00 PM	25	1.08
4	03/08/2011 12:00 PM	04/08/2011 03:00 PM	27	1.17
Sheet1

Columns A and B are custom formatted :dd/mm/yyyy hh:mm AM/PM
Values are entered like: 03/08/2011 13:00:00

Column C just takes the time difference between the date in hours. custom formatted: [h]

As Column C is a still a time we need to use the INT() function to convert it before calculating the number of days in column D.

Hope this helps to get you started.

Bertie

Edit: To round the number of days off to a whole number you can wrap the CEILING function around the formula in column D

=CEILING(C4+INT(C4)/24,1)