Hi
I am trying to create a car rental spreadsheet for my own use but this one may be abit too complicated for me.
I need the excel to help me to calculate the number of days and hours the car is used.
Example:
pickup car at 12 august 2009 at 9am in the morning
return car at 14 august 2009 at 11am in the morning
Output Total = 2 days + 2 hours rental
The confusing part for me is having the extra 2 hours calculated.
any help?
A1 = 8/12/2009 9:00:00 AM
B1 = 8/14/2009 11:00:00 AM
C1 =B1-A1
Highlight C1 and press CTRL-1 to open the format cell.
Apply a custom number format of:
d "days + "h "hours"
======
Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you should edit that post above and change the title to something that indicates what this topic is. It's not car rentals. It's "adding/subtracting times over several days."
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
In addition to JB's post...
If you want the resulting output in "days and hours" format then the next part will depend on whether total days can ever exceed 31 ?
No:
use format on C1 of: d "Day(s) & "h "Hour(s)"
(or as already outlined by JB)
Yes:
probably best to use an adjacent formula given you can not show cumulative days above 31 via a Custom Format, ie
D1: =INT(C1)&" Day(s) & "&HOUR(MOD(C1,1))&" Hour(s)"
In the above we're assuming complete hours or rounded down hours, unspecified.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Oh thanks! I will try it out later.
I am getting some trouble with the
total duration of rental time * rental rate
the output didn't turn out what i expected.
please do look into the attachment for the one i did.
in the excel spreadsheet,
($100 x 3 days) + (2 hours x $10) = $339.17
Isn't it suppose to be $320.00 ?
The formula in I17 could be
=INT(C17-B17)*G17 + MOD(C17-B17, 1) * 24 * H17
... if you charge for fractional hours, or
=INT(C17-B17)*G17 + ROUND(MOD(C17-B17, 1) * 24, 0) * H17
... if you round to the nearest hour, or
=INT(C17-B17)*G17 + CEILING(ROUND(MOD(C17-B17, 1) * 24, 6), 1) * H17
... if you round to the next whole hour.
Entia non sunt multiplicanda sine necessitate
ah that works well ! thanks.
and now i got the basic thing up. need to add another complicated matter.
I need to set if the (rental hours) exceed 5 hours
then Rental Day will be + 1
meaning, if the rental is more than 5 hours, it will be charged as full day.
tried something like this but.. i know its not right.
=IF(E18<5, D18+1, FALSE)
Last edited by limecity; 11-18-2009 at 01:05 PM.
Maybe as attached.
appreciate your time and effort putting into this problem. :-D
problems solved now :-D thank you very much
You're welcome. Would you take a minute to read the forum rules, and then mark the thread as Solved?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
limecity 
Register To Reply
Bookmarks