Excel Time Calculations

[CarFleet]

I am trying to design a basic time recording spreadsheet.
I habve a morning start time, morning finish time, lunchtime then afternoon start and finish time. This is set against a target time of 37 hours per week. The times are formatted as [h]:mm:ss. If the week's time exceeds 37 hours, excel calculates the difference, but if the week's time is less than 37 hours, the cell shows: ########.
Any ideas?

Thanks [sig][/sig]

 

[BobCrawf]

If you could show us the formula you are using we may be able to give you some idea where the problem is.

If you expand the column that has the ######## in it you will probably see some kind of error message. [sig][/sig]

 

[CarFleet]

I understand what you mean by expanding the cell, but if I do that I just get more #.
The formula I have used is weekly total = 51:15:00 (which is the total hours for one week, subtracted from the tasrget of 37:00:00 which gives me 14:15:00. The problem is if the weekly total is less than 37:00:00 e.g. 35:00:00 subtracted fronm 37:00:00 doesn't give me (02:00:00)but the series of hash signs, ######.
Is it possible to send the spreadsheet to you? It is only 25kb. [sig][/sig]

 

[Ruairi]

You said
The formula I have used is weekly total = 51:15:00 (which is the total hours for one week, subtracted from the tasrget of 37:00:00 which gives me 14:15:00.

but if youre getting 14:15:00 it is actually WeeklyTotal - 37:00:00 so if weekly total is 35:00:00
35:00:00 - 37:00:00 is -2:00:00, not 2:00:00 as you are expecting.

I would expect to get an error when trying to format a negative number as a time. You cant have a negative time.

You can email me the spreadsheet if you like and i will take a look at it. [sig]<p>Ruairi<br><a href=mailto:ruairi@logsoftware.com>ruairi@logsoftware.com</a><br>[/sig]

 

[BobCrawf]

You can send the workbook to me if you would like. I'll fix the problem and return it to you.

Bob Crawford
bobc@san.rr.com [sig][/sig]

 

[CarFleet]

In the meantime, I've done it!
If you use the 1904 date system, it works.
See:

http://www.pcworld.com/resource/article.asp?aid=7222.

Thanks for your replies. [sig][/sig]

 

[Dreamboat]

http://www.cpearson.com/excel/topic.htm

The BEST site for timesheets and other Excel stuff. FREE. He's even got a downloadable timesheet that'll calculate overtime and everything. [sig]<p> <br><a href=mailto:techsupportgirl@home.com>techsupportgirl@home.com</a><br><a href=

http://www.wedomanuals.com>

</a><br> [/sig]

 

[tapks]

Really a very good site, which can solve a lots of problems in Excel.

TapanKS