Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
timesheet problems!
hi
I have managed to set up a timesheet (all by myself woohoo!!). Having a few problems though! I have a rolling timesheet over a 4-5 week period. It adds up the daily total no problem, but for the daily difference, if it is a negative then it shows up as ######. anyone any ideas? thanks msiz (",) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
timesheet problems!
What formula are you using that returns ##### for negative values? How is
the cell formatted which returns #####? Dave -- Brevity is the soul of wit. "msiz" wrote: hi I have managed to set up a timesheet (all by myself woohoo!!). Having a few problems though! I have a rolling timesheet over a 4-5 week period. It adds up the daily total no problem, but for the daily difference, if it is a negative then it shows up as ######. anyone any ideas? thanks msiz (",) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
timesheet problems!
hi Dave
using this formula: ie. =F5-$G$1 formatted as [h]:mm msiz "Dave F" wrote: What formula are you using that returns ##### for negative values? How is the cell formatted which returns #####? Dave -- Brevity is the soul of wit. "msiz" wrote: hi I have managed to set up a timesheet (all by myself woohoo!!). Having a few problems though! I have a rolling timesheet over a 4-5 week period. It adds up the daily total no problem, but for the daily difference, if it is a negative then it shows up as ######. anyone any ideas? thanks msiz (",) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
timesheet problems!
Well, time can't be formatted as negative, so that's why you're getting the
error value. Time begins at point 0, not at negative infinity, so Excel can only return an error. One way to work around this is to convert the calculation into numerical form. Example: A1= 2:00 AM B1= 1:00 AM C1 = B1-A1 -- returns #### if formatted as time. Changing this calculation to =(B1-A1)*24 converts the time into digit form and returns -1. Dave -- Brevity is the soul of wit. "msiz" wrote: hi Dave using this formula: ie. =F5-$G$1 formatted as [h]:mm msiz "Dave F" wrote: What formula are you using that returns ##### for negative values? How is the cell formatted which returns #####? Dave -- Brevity is the soul of wit. "msiz" wrote: hi I have managed to set up a timesheet (all by myself woohoo!!). Having a few problems though! I have a rolling timesheet over a 4-5 week period. It adds up the daily total no problem, but for the daily difference, if it is a negative then it shows up as ######. anyone any ideas? thanks msiz (",) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
timesheet problems!
Hi
You cannot have negative time, unless you are using the 1904 date system ToolsOptionsCalculation1904 If your negative time is caused by times running over a 24 hour period, then try =MOD(F5-$G$1,1) -- Regards Roger Govier "msiz" wrote in message ... hi Dave using this formula: ie. =F5-$G$1 formatted as [h]:mm msiz "Dave F" wrote: What formula are you using that returns ##### for negative values? How is the cell formatted which returns #####? Dave -- Brevity is the soul of wit. "msiz" wrote: hi I have managed to set up a timesheet (all by myself woohoo!!). Having a few problems though! I have a rolling timesheet over a 4-5 week period. It adds up the daily total no problem, but for the daily difference, if it is a negative then it shows up as ######. anyone any ideas? thanks msiz (",) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
timesheet problems!
thank you so much - this worked!
msiz x "Roger Govier" wrote: Hi You cannot have negative time, unless you are using the 1904 date system ToolsOptionsCalculation1904 If your negative time is caused by times running over a 24 hour period, then try =MOD(F5-$G$1,1) -- Regards Roger Govier "msiz" wrote in message ... hi Dave using this formula: ie. =F5-$G$1 formatted as [h]:mm msiz "Dave F" wrote: What formula are you using that returns ##### for negative values? How is the cell formatted which returns #####? Dave -- Brevity is the soul of wit. "msiz" wrote: hi I have managed to set up a timesheet (all by myself woohoo!!). Having a few problems though! I have a rolling timesheet over a 4-5 week period. It adds up the daily total no problem, but for the daily difference, if it is a negative then it shows up as ######. anyone any ideas? thanks msiz (",) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named range causing really weird problems - HELP | Excel Discussion (Misc queries) | |||
Timesheet Help. Rounding to the nearest 1/4 | Excel Worksheet Functions | |||
displaying selected items from a yearplanner in a timesheet | Excel Discussion (Misc queries) | |||
overtime on timesheet | Excel Worksheet Functions | |||
timesheet with running total of overtime | Excel Worksheet Functions |