Subtracting hours and minutes in Excel?

Question

I rent out office space by the hour. Some of my clients purchase a "block" of hours in advance and then I deduct their office time from that. I am trying to create an Excel spreadsheet that will keep track of how many hours they have remaining, but I cannot get it to work right. I have it calculating the number of hours correctly, but I can't get a runng balance (which will eventually be reduced to 0). How can I get this to work right? Thanks!

Answer 1

Your cells should be formatted as hh:mm. This is a 24 hour clock.



Say your start time and end time are in A1 and A2.

09:00 17:30

Your client's start time and end time are in B1 and B2.

09:00 12:00

Your total available hours are in C1

=A2-A1

Your client's total hours are in C2

=B2-B1

your remaining available hours are in C3

=C1-C2



The result using my example will be

C1= 08:30

C2= 03:00

C3 = 05:30



As soon as your clients use all the available time, C3 will be reduced to 0:00

Answer 2

you dont really give any detail on how your sheet is setup.



so generally...to get a running balance you would

1. sum() the number of hours...these are the number of hours you are already calculating. Assume these hours are in A1 to A31



this sum formula goes in a cell by itself like B2 and would look like this =SUM(A1:A31)



2. you have to have a starting cell that shows how many hours they have to start with. this would be in cell B1



3. the remaining hours would just be =B1-B2

or if you didnt want to show B2 then you can put it together something like this =B1-SUM(A1:A31)



hope that helps



edit-

ok i downloaded the file.

your problem is that E2 is formatted as a number

you need to change E2 to time...so try this

- in E2 type in 25:00

- this should make E2 turn into a wierd looking 1.0

- right click on E2

- click format cells

- click Custom

- in the Type box copy&paste this



[h]:mm



click ok and it should now say 25:00



you will notice that E5 changes to 0

- then copy E2

- select E5 to E20

- rightclick on the highlight

- click paste special

- click Formats

- click ok, and you are done.



using this formatting will will show hours and minutes only.

putting the h in brackets [h] says to use hours and minutes only

the reason that putting 25:00 in the original formatting showed 1....its because 25:00 is 1 AM of the next day in military times 24 hour clock ( and of course a 24 hour clock will max out at 24 hours)

so when you subtracted 1, you noticed that E5 dropped to 0...thats actually midnight (or 0:00 on a 24 hour clock)



once thats all done, all you have to remember to do is enter E2 with the colon (like 25:00) and not just 25



that would be the way i would recommend



but if you really just want to enter a decimal whole number 25 in E2

then just change the formula in E5



=E2/24-D5



all your other formulas are right.

either way, since you can obviously purchase over 24 hours, then you should change the format to the one above anyway

Answer 3

assuming your formula works correctly otherwise, =if(MOD(C7-B7,1)*24>=6,MOD(C7-B7,1)*24 -1,MOD(C7-B7,1)*24 )