Home |
Search |
Today's Posts |
#1
|
|||
|
|||
working with time functions
i am making a spreadsheet of hours people have worked in a week. People
should work 37 hours, anything above that is credit, and below it debit. In my spreadsheet, the "hours worked" cell is displayed in 37:00 format (as is all other times in the worksheet) if someone works 37:30, that 30 minutes is displayed in the credit/debit cell fine, but if someone works less than 37:00 in the week nothing i do works to show the debit people are accrueing. So if someone works 35:00 i would like it to show that they are in 02:00 debit. Has anyone got any suggestions? |
#2
|
|||
|
|||
Chris,
I guess that the problem you get for a it is seeing ####. This is not a problem apart from presentation, there is still a negative time in there with you can do arithmetic on. One way would be to work in decimal hours, =(B1-A1)*24, and format as general. A better way would be to have a credit and debit cell separately, and then sum those and take individually and subtract debit from credit. =(B1-A10)*(B1-A1) =(A1-B10)*(A1-B1) -- HTH RP (remove nothere from the email address if mailing direct) "Chris01623" wrote in message ... i am making a spreadsheet of hours people have worked in a week. People should work 37 hours, anything above that is credit, and below it debit. In my spreadsheet, the "hours worked" cell is displayed in 37:00 format (as is all other times in the worksheet) if someone works 37:30, that 30 minutes is displayed in the credit/debit cell fine, but if someone works less than 37:00 in the week nothing i do works to show the debit people are accrueing. So if someone works 35:00 i would like it to show that they are in 02:00 debit. Has anyone got any suggestions? |
#3
|
|||
|
|||
In Excel, time is a formated decimal fraction of 24 hour days so credit
would be =IF(worked37/24,worked-37/24,"") and debit would be =IF(worked<37/24,37/24-worked,"") Jerry Chris01623 wrote: i am making a spreadsheet of hours people have worked in a week. People should work 37 hours, anything above that is credit, and below it debit. In my spreadsheet, the "hours worked" cell is displayed in 37:00 format (as is all other times in the worksheet) if someone works 37:30, that 30 minutes is displayed in the credit/debit cell fine, but if someone works less than 37:00 in the week nothing i do works to show the debit people are accrueing. So if someone works 35:00 i would like it to show that they are in 02:00 debit. Has anyone got any suggestions? |
#4
|
|||
|
|||
"?B?Q2hyaXMwMTYyMw==?="
wrote in : i am making a spreadsheet of hours people have worked in a week. People should work 37 hours, anything above that is credit, and below it debit. In my spreadsheet, the "hours worked" cell is displayed in 37:00 format (as is all other times in the worksheet) if someone works 37:30, that 30 minutes is displayed in the credit/debit cell fine, but if someone works less than 37:00 in the week nothing i do works to show the debit people are accrueing. So if someone works 35:00 i would like it to show that they are in 02:00 debit. Has anyone got any suggestions? You can change a setting in Excel to show negative time: http://support.microsoft.com/default...b;en-us;182247 Doing so has a drawback but I cannot remember what it is. -- It is I, DeauDeau (Free after monsieur Leclerc in 'Allo, 'allo) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use solver to schedule part time and full time workforce? | Excel Discussion (Misc queries) | |||
how do i count how many people are working between two times in e | Excel Worksheet Functions | |||
Working w/ non-standard Time Format | Excel Worksheet Functions | |||
Time Sheets | Excel Discussion (Misc queries) | |||
Time Functions | Excel Worksheet Functions |