I created daily (runs for a year) Timesheet containing leave, holiday, flexi, etc, but cells containing formula for days that are yet to be filled are in red (minus). Try to use "IF(J10<>0 .. - this works, but not perfect. I need to use IF(J10<0:00) but excel doesnt accept it. Please assist. How can I attach / post the timesheet? gringsing

I built an Excel timesheet many years ago and I understand the difficulty of time conversions. The trick is to use the HOUR and MINUTE functions to extract the number of hours and the number of minutes. I am making the assumption that J10 is cummulative hours and not a time. In other words, if J10 = 5:30, then that means five hours and 30 minutes - not 5:30 AM. So in your example I would use the following: =IF( (HOURS(J10)+MINUTES(J10)) <> 0, 'then', 'else') Michael J

Sorry for being so unclear about my timesheet. I have columns: cummulated hours (+ OR - using [h]:mm;;h:mm for + or [Red][h]:mm;;h:mm) for -), day, date, in, out, in, out, Leave (for annual leave, pub hold, etc), daily hrs worked, required hrs, daily calculated over or under required hours (- OR + using previous format). Without the formula, all work fine. However, to avoid seeing 'red', I use formulas that if the day is not worked, then daily hrs worked should be 0:00, and the daily calculated under required hrs will be BLANKS instead of red (negatives). the formula: =IF(J19<>0,(J19-K19),"") works fine, however... I need the 0:00 value of J column to be calculable, so that if an employee is taking a flexi day off, He or she would lose the required hours (negatives). So what I need is to have: =IF(J19<0:00,(J19-K19),"") The =IF(J19<0,(J19-K19),"") does not calculate, while Excel does not allow J19<0:00 Oooopsss sorry, I got it all wrong... it wouldnt work - because by default the value of (result of calculation) the daily hours work (J) is 0:00 ... thus although I get the NON-RED effect, none would be calculated. Of course, I dont need any of those formula if required daily hours is not pre-enterred (which users find it annoying). I am so sorry.... any better ideas? Many thanks, Kiai

Put 0:00 in anther cell and then reference that cell in your formula. Or try something like this, =IF(J19<VALUE("0:00"),(J19-K19),"")

Michael J and Grok Lobster ... many thanks for your formula, trick and suggestion. Combining all those... my Timesheet is happily smiling... and so am I. :))) Kiai