Another day, another problem.

JMo

New Member
Joined
Apr 3, 2002
Messages
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<C10))-(E10-D10+(E10<D10)))*24

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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi Julia,

Have you check the general cell formating. You know "Format"->"Cells"? sounds like that kind of problem.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top