Excel 2007 : Problem with time cell formula.

[rhyler1]

Hello,

I need help with a formula. I have a workbook that has 2 sheets. First sheet is a work schedule. No problem with that sheet. The problem is that when I pull the information for the daily hours to the second sheet. If there is a time in the cell that it is pulling from it returns the correct info. But if that employee is not scheduled that day it returns 12:00 AM every time. Any suggestions will be greatly appreciated.

Thanks in advance.

[MarvinP]

Hi rhyler1 and welcome to the forum.

12:00 AM is really what reports back if the cell is zero. It sounds like your formula or VLookup or whatever doesn't find a time and comes back with a zero, which shows as 12:00 AM when formatted as a time.

You might do a short If statement that would display a blank if zero was found. If you need help with that, please put up a small example - Press Go Advanced and click the PaperClip Icon above the message area.

[Cutter]

It is returning a zero which is being displayed as 12:00 AM because that is what you get when 0 is formatted as time.

You have to place whatever formula you are using to get that number within an IF() function to test for a zero value.

=IF("your formula"=0,"","your formula")

[DonkeyOte]

You might also consider applying a Custom Format to your result cell so as to avoid need for double evaluation: [h]:mm:ss;;;@
(modify format to suit of course)

It should be noted that using this method the underlying 0 persists but is not visible to end user ... a by product of this approach is that you have a consistent data type in your result cells.

[rhyler1]

Thanks for the quick response. My formula is =SUM(Schedule!C10).

Thanks

[MarvinP]

Hi rhyler1

Your formula is only a single cell (C10) on the Schedule worksheet. You might not need the Sum( ) part of the formula. Try a simple = Schedule!C10 and see what happens.

Excel is cool!!

[rhyler1]

Thanks again. Unfortunately it still returns 12:00 AM

[Cutter]

This returns 12:00 AM?

=IF(Schedule!C10=0,"",Schedule!C10)

or the suggestion made by DonkeyOte in Post #4

[rhyler1]

Thanks that works. The formula works like I expected it to by leaving the two cells, in time and out time, B6 and C6 respectively blank on sheet 2. D6 calculates the hours worked minus .5 hours for lunch if works more than 6 hours. Now that the cells B6 and C6 on sheet 2 show no time the formula for
D6 =(C6-B6)*24-IF((C6-B6)*24>8,0.5,IF((C6-B6)*24>6.49,0.5,0)) was blank previously now it shows #VALUE! Would it be possible for D6 to show either blank or 0?

[Cutter]

It sounds like you just need:

=IF(C6="","",(C6-B6)*24-IF((C6-B6)*24>8,0.5,IF((C6-B6)*24>6.49,0.5,0)))

[rhyler1]

Thanks! It worked like a charm. Just wish I knew how to figure that out on my own.

[Cutter]

You're welcome. It'll come.

Don't forget to mark your thread as SOLVED (see FAQ in menu bar above for directions)