Another day, another problem.

JMo · May 8th, 2002, 07:21 AM

So, obviously Excel is not my forte.

I'm still working on my timesheet, and have run into a new and exciting problem.

The timesheet has these columns:
Time In
Time out for lunch
Time in from lunch
Time Out

Then, it calculates the hours worked using this formula:

=((F10-C10+(F10
Works great. BUT, ran into a weird problem last night that I can't figure out.

Yesterday, I worked from 8:00 am to 12:00 am (midnight) last night. When I enter these as my times in and out, the formula calculates and displays just fine -- but the 12:00 am does NOT display in the Time Out column. I see it in the formula bar, and it obviously is there because the formula is calculating correctly. I tested other times, and all others display...but 12:00 am does not.

I've checked conditional formatting, but there's nothing there.

What am I missing????

MUCH thanks,

Julia

Rocky E · May 8th, 2002, 07:34 AM

Hi Julia,

Have you check the general cell formating. You know "Format"->"Cells"? sounds like that kind of problem.

JMo · May 8th, 2002, 07:59 AM

Thanks...have checked the formatting...nothing unusual, and nothing that looks like it would cause this.

As I mentioned, the cell will display any time other than 12:00 am -- and it does "see" 12:00 am because the formula calculates correctly and the value displays in the formula bar.

I also noticed that all of the Time In/Out cells are formatted identically -- and if I enter 12:00 am in any other cell (e.g., Time In), it does not display there either. So, all times other than 12:00 am display in all of those cells...12:00 am does not display in ANY of them.

Hope this additional info helps...

Thanks!!

Julia

Asala42 · May 8th, 2002, 08:05 AM

G'day,

It turns out that 12:00 AM is equal to 0 when formated as a number.

Go to your Tools/Options/View Tab and see if you have the windows option to view "zero values" - I'm guessing that it is currently not checked.

Adam

Steve Hartman · May 8th, 2002, 08:06 AM

You say it doesn't display. Do you mean the cell is blank, it displays something other than 12:00 am, etc? What exactly does the cell display?

JMo · May 8th, 2002, 08:08 AM

The zero value option fixed it! Honestly didn't think of that because the cells are formatted as time, not general number, so I would never have thought that it was seeing 12:00 am as the number 0.

Live and learn...Thanks to all!!

Julia

May 8th, 2002, 07:34 AM	#2
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Hi Julia, Have you check the general cell formating. You know "Format"->"Cells"? sounds like that kind of problem. __________________ Hope this helps! Rocky <h6>"Be not the first by whom the New are try'd, Nor yet the last to lay the Old aside." Alexander Pope (1688-1744).</h6>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 8th, 2002, 07:21 AM	#1
JMo New Member Join Date: Apr 2002 Posts: 17	So, obviously Excel is not my forte. I'm still working on my timesheet, and have run into a new and exciting problem. The timesheet has these columns: Time In Time out for lunch Time in from lunch Time Out Then, it calculates the hours worked using this formula: =((F10-C10+(F10 Works great. BUT, ran into a weird problem last night that I can't figure out. Yesterday, I worked from 8:00 am to 12:00 am (midnight) last night. When I enter these as my times in and out, the formula calculates and displays just fine -- but the 12:00 am does NOT display in the Time Out column. I see it in the formula bar, and it obviously is there because the formula is calculating correctly. I tested other times, and all others display...but 12:00 am does not. I've checked conditional formatting, but there's nothing there. What am I missing???? MUCH thanks, Julia

May 8th, 2002, 07:59 AM	#3
JMo New Member Join Date: Apr 2002 Posts: 17	Thanks...have checked the formatting...nothing unusual, and nothing that looks like it would cause this. As I mentioned, the cell will display any time other than 12:00 am -- and it does "see" 12:00 am because the formula calculates correctly and the value displays in the formula bar. I also noticed that all of the Time In/Out cells are formatted identically -- and if I enter 12:00 am in any other cell (e.g., Time In), it does not display there either. So, all times other than 12:00 am display in all of those cells...12:00 am does not display in ANY of them. Hope this additional info helps... Thanks!! Julia

May 8th, 2002, 08:05 AM	#4
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,030	G'day, It turns out that 12:00 AM is equal to 0 when formated as a number. Go to your Tools/Options/View Tab and see if you have the windows option to view "zero values" - I'm guessing that it is currently not checked. Adam

May 8th, 2002, 08:06 AM	#5
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	You say it doesn't display. Do you mean the cell is blank, it displays something other than 12:00 am, etc? What exactly does the cell display?

May 8th, 2002, 08:08 AM	#6
JMo New Member Join Date: Apr 2002 Posts: 17	The zero value option fixed it! Honestly didn't think of that because the cells are formatted as time, not general number, so I would never have thought that it was seeing 12:00 am as the number 0. Live and learn...Thanks to all!! Julia