Excel

Question

Hi.  I work for a car share club which requires me to input a "start time" (eg 12.00) in column A, the  "finish time" (17.15) in column B, total the number of hours used in column C and then multiply the hours x £0.20 in column D.  Can anyone tell me how to write formulas for these actions please?

Answer 1

Sara wrote:

not sure how to put the date and time into the same cell though?

Simply 11/10/2012 15:15 or 11/10/2012 3:15 PM.  Use the appropriate short-date form for your region.

Sara wrote:

are you able to give me a seperate sum for column C (B-A) and D (C x 0.20)?

Sorry.  You did say that the first time, but I missed it.  Also, it seems that I misinterpreted what is in column D:  it is total cost, not the hourly rate.

Previously, you wrote:  "total the number of hours used in column C".  I interpret that to mean that you want to display elapsed time a decimal number of hours, not in the form h:mm.

Do one of the following depending on whether you have date/time or just time (within a single 24-hour period) in A2 and B2.

C2:  =(B2-A2)*24

or

C2:  =(B2-A2+(A2>B2))*24

ideally formatted as Number instead of General so that you can control the appearance.

Then D2 is simply:

=ROUND(C2*0.2,2)

formatted as Currency if you wish.

PS:  If you have many such calculations in column D, I suggest that you put the hourly rate (0.20) into some cell (e.g. X1), then reference that cell as follows:

=ROUND(C2*$X$1,2)

The use of $X$1 instead of X1 makes it easy to copy the formula down column D.

Answer 2

thanks so much!  not sure how to put the date and time into the same cell though?  and are you able to give me a seperate sum for column C (B-A) and D (C x 0.20)?

Answer 3

Sara wrote:

Hi.  I work for a car share club which requires me to input a "start time" (eg 12.00) in column A, the  "finish time" (17.15) in column B, total the number of hours used in column C and then multiply the hours x £0.20 in column D.  Can anyone tell me how to write formulas for these actions please?

First, you should enter times in the form 12:00 and 17:15, using a colon instead of a period to separate hours and minutes.

Unless your regional settings use period as the normal setting.  Is that the case?

Second, it would be better enter the date along with the time, just in case it spans more than one day or it spans across midnight.  You can format the cell to show just the time, if you wish.

Then, if the start and end times are in A2 and B2 and the rental rate is in D2, the total cost is:

=ROUND((B2-A2)*24*D2,2)

If you cannot enter the date as well as time, the following should handle any rental less than 24 hours, even if it spans midnight:

=ROUND((B2-A2+(A2>B2))*24*D2,2)