time calculation

[Guest]

I am creating a spreadsheet which will log my time in and out of my client's offices. I sell blocks of hours to most of my customers and I want them to know what my time in and out is, how long i was there, how many hours they have used so far, and how many hours they have remainin

I have it set up so i enter my "time in" in one column and my "time out" in another....giving my "total hours" onsite in the next column.....which then tallys up the totals for the time used so far at the bottom of the sheet....how can i calculate the remaining time
for example: the "total time used" cell shows 10 hours used =SUM(K16:K36) ....if they start with 40 hours how can i subtract the 10 hours from the 40 hours and keep it in an hour:minute format
Thank you for your help...i have tried everything and it is driving me NUTS!!!

 

[David McRitchie]

Hi Dexter,
format as [h]:mm
enter 40 hours as 40:00
the square brackets keep hours from overflowing into days.

More information on date and time
http://www.mvps.org/dmcritchie/excel/timedate.htm

I am creating a spreadsheet which will log my time in and out of my client's offices. I sell blocks of hours to most of my

customers and I want them to know what my time in and out is, how long i was there, how many hours they have used so far, and how
many hours they have remaining

I have it set up so i enter my "time in" in one column and my "time out" in another....giving my "total hours" onsite in the next

column.....which then tallys up the totals for the time used so far at the bottom of the sheet....how can i calculate the remaining
time?

for example: the "total time used" cell shows 10 hours used =SUM(K16:K36) ....if they start with 40 hours how can i subtract

the 10 hours from the 40 hours and keep it in an hour:minute format?

 

[Guest]

thanks for the help....but if I entered 1 into the total hours used cell I was getting 23 for an answer when I used the format...so I played with the numbers and used this: =1.667-J37 and it gives me 40 for total hours remaining when no times have been entered and it subtracts fine when I enter hours...I don't know what the 1.667 has to do with this working properly but it is working...thanks for your help...

 

[David McRitchie]

Hi Dexter,
You would enter 40 hours as 40:00 (40 hours, 0 minutes)
when you entered 1.667 your are entering 1.667 days, which
is as close to 40 hours as your fraction is 1 2/3.

All covered on my Date and Time page (and Chip Pearson has one too)
.http://www.mvps.org/dmcritchie/excel/datetime.htm
http://www.cpearson.com/excel/datetime.htm
either place you will see that date and time is measured in days
and fractions of days from a starting point.

A 1 should have shown up as 24:00 or with a format of only hh:dd
you would see 0:00 since the hours would have overflowed into days
which you won't see as hours unless you format as [h]:dd

thanks for the help....but if I entered 1 into the total hours used cell I was getting 23 for an answer when I used the

format...so I played with the numbers and used this: =1.667-J37 and it gives me 40 for total hours remaining when no times have been
entered and it subtracts fine when I enter hours...I don't know what the 1.667 has to do with this working properly but it is
working...thanks for your help...