Midnight disappears

S

Shirley Zaknich

I am using Excel 2002 - and working with times. I have formatted the times
to hh:mm AM/PM and when I enter 0:00 to get 12:00 AM it works good. But when
I hide the zeros the midnight time disappears. I need midnight to remain as
a figure as I need it to calculate the difference.

Example
A1 = 12:00 AM
A2 = 10:00 AM
A3 = 10:00 hour working day

If I have the zeros showing it makes for a very busy timesheet.

Anyone got any suggestions. Thanks to all who help.
 
J

Jim Rech

I need midnight to remain as a figure as I need it to calculate the
You could enter a time very near to midnight, such as 12:00:00.001 AM.

Of course, since midnight is zero there is nothing to be gained by
subtracting it from another time.

--
Jim Rech
Excel MVP

message |I am using Excel 2002 - and working with times. I have formatted the times
| to hh:mm AM/PM and when I enter 0:00 to get 12:00 AM it works good. But
when
| I hide the zeros the midnight time disappears. I need midnight to remain
as
| a figure as I need it to calculate the difference.
|
| Example
| A1 = 12:00 AM
| A2 = 10:00 AM
| A3 = 10:00 hour working day
|
| If I have the zeros showing it makes for a very busy timesheet.
|
| Anyone got any suggestions. Thanks to all who help.
 
S

Shirley Zaknich

Thanks for your answer Jim, I had thought of that, but I thought there may
have been some other way. Can you plse explain by what you meant by ... ...
since midnight is zero there is nothing to be gained by subtracting it from
another time ... My scenario is that the employee starts at 5 pm on say a
Monday, and finishes 10:00 am the following morning, on the timesheet I enter
the 5 to midnight on the Monday, and then the Midnight to 10:00 on Tuesday (I
need for the midnight to be visible), hence my disapperance.

Thanks anyway Jim, all information is very much appreciated.
 
E

Eli

Shirley,

I'm not one of the experts and I'm new to the forum but it may help to
provide some more detail about your problem such as how you are hiding
the zeros that causes midnight to disappear or what zero values that
you don't want to display.

One thing to try if you haven't yet is to use a custom number format to
set the format for the data in a given cell depending on the data type.
Go to your Excel help for details and search for custom number format,
create and delete.

The general form of the format code would be
+number;-number;0;text (those are semicolons separating the data
types)
and you can specifiy the format for each individual type of data.

So, if you wanted to use an elapsed time format that did not display a
zero value then
[h]:mm;; (those are semicolons following the mm)
which specifies a format for a positive number but does not specify a
format for a negative number or a zero.

You might also find this link of interest
http://www.exceltip.com/st/Calculating_the_difference_between_hours_in_Microsoft_Excel/97.html
 
J

Jim Rech

Midnight has a value of 0, 6AM of .25, Noon is .5, etc. Time in Excel means
a part of a day. Put .5 in a cell and time format it and you'll see noon.
Put midnight in a cell and General format it and you'll see 0.

So since all zeros disappear (including time formatted ones) when you choose
not to display zeros you have to enter midnight with a decimal as in my
first response or at text (for appearance sake). If a time span starts at
midnight and ends at 10AM the hours worked are ten. You only need to look
at the end time.
--
Jim Rech
Excel MVP
message | Thanks for your answer Jim, I had thought of that, but I thought there may
| have been some other way. Can you plse explain by what you meant by ...
....
| since midnight is zero there is nothing to be gained by subtracting it
from
| another time ... My scenario is that the employee starts at 5 pm on say a
| Monday, and finishes 10:00 am the following morning, on the timesheet I
enter
| the 5 to midnight on the Monday, and then the Midnight to 10:00 on Tuesday
(I
| need for the midnight to be visible), hence my disapperance.
|
| Thanks anyway Jim, all information is very much appreciated.
|
| "Jim Rech" wrote:
|
| > >>I need midnight to remain as a figure as I need it to calculate the
| > >>difference.
| >
| > You could enter a time very near to midnight, such as 12:00:00.001 AM.
| >
| > Of course, since midnight is zero there is nothing to be gained by
| > subtracting it from another time.
| >
| > --
| > Jim Rech
| > Excel MVP
| >
| > message | > |I am using Excel 2002 - and working with times. I have formatted the
times
| > | to hh:mm AM/PM and when I enter 0:00 to get 12:00 AM it works good.
But
| > when
| > | I hide the zeros the midnight time disappears. I need midnight to
remain
| > as
| > | a figure as I need it to calculate the difference.
| > |
| > | Example
| > | A1 = 12:00 AM
| > | A2 = 10:00 AM
| > | A3 = 10:00 hour working day
| > |
| > | If I have the zeros showing it makes for a very busy timesheet.
| > |
| > | Anyone got any suggestions. Thanks to all who help.
| >
| >
| >
 
S

Sandy Mann

Hi

If I am understanding you properly then with your start time, 5 PM, in cell
C2 and the finish time, 10 AM, in cell D2 the formula:

=D2-C2+(D2<C2)

or less intuitively:

=MOD(D2-C2,1)

with a custom format of [h]:mm will give the total number of hours worked
even if it spans midnight.
(The square brackets prevent the hours rolling over into a day when they get
to 24.)

If you want to do it as individual days then to calculate the number of
hours to midnight on Monday ie 5 PM to Midnight use:

=1-C2

which, formatted as h:mm will give the number of hours to midnight, (one (1)
whole day minus the start time), and as Jim said, because Midnight is
starting out as zero and time on Tuesday will by just that number of hours
so the formula will be simply:

=D2 formatted as h:mm

The two days' hours can then be added in a cell formatted as [h]:mm as:

=(Monday's hours) + (Tuesday's hours)

HTH

Sandy
 
S

Shirley Zaknich

Thanks Sandy for your response. It's great to get so much feedback... and
interesting feedback. Work with times and dates on a timesheet in a roster
system is new to me and I have learned so much from you all.

My main concern was the midnight disappearing after I remove the zero's from
sight, therefore I did not say that on the previous day the person did start
at 5:00pm, but finished at 10:00 AM the next day.

For example, A1 = 5:00 PM
A2 = Midnight or 12:00 AM

B1 = Midnight or 12:00 (The zero factor... 0:00 AM which hides)
B2 = 10:00 AM

I always got the right hours calculated, but being a timesheet, I wanted the
0:00 AM to be visible to the person, and not left as a blank cell.

Thanks for your tip. I was impress with "MOD" and the "=1-C2" responses.

Thanking you again.

Sandy Mann said:
Hi

If I am understanding you properly then with your start time, 5 PM, in cell
C2 and the finish time, 10 AM, in cell D2 the formula:

=D2-C2+(D2<C2)

or less intuitively:

=MOD(D2-C2,1)

with a custom format of [h]:mm will give the total number of hours worked
even if it spans midnight.
(The square brackets prevent the hours rolling over into a day when they get
to 24.)

If you want to do it as individual days then to calculate the number of
hours to midnight on Monday ie 5 PM to Midnight use:

=1-C2

which, formatted as h:mm will give the number of hours to midnight, (one (1)
whole day minus the start time), and as Jim said, because Midnight is
starting out as zero and time on Tuesday will by just that number of hours
so the formula will be simply:

=D2 formatted as h:mm

The two days' hours can then be added in a cell formatted as [h]:mm as:

=(Monday's hours) + (Tuesday's hours)

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


Shirley Zaknich said:
Thanks for your answer Jim, I had thought of that, but I thought there may
have been some other way. Can you plse explain by what you meant by ... ....
since midnight is zero there is nothing to be gained by subtracting it from
another time ... My scenario is that the employee starts at 5 pm on say a
Monday, and finishes 10:00 am the following morning, on the timesheet I enter
the 5 to midnight on the Monday, and then the Midnight to 10:00 on Tuesday (I
need for the midnight to be visible), hence my disapperance.

Thanks anyway Jim, all information is very much appreciated.
Click to expand...
Click to expand...
 
S

Shirley Zaknich

Jim Rech said:
Midnight has a value of 0, 6AM of .25, Noon is .5, etc. Time in Excel means
a part of a day. Put .5 in a cell and time format it and you'll see noon.
Put midnight in a cell and General format it and you'll see 0.

So since all zeros disappear (including time formatted ones) when you choose
not to display zeros you have to enter midnight with a decimal as in my
first response or at text (for appearance sake). If a time span starts at
midnight and ends at 10AM the hours worked are ten. You only need to look
at the end time.
--
Jim Rech
Excel MVP
message | Thanks for your answer Jim, I had thought of that, but I thought there may
| have been some other way. Can you plse explain by what you meant by ...
....
| since midnight is zero there is nothing to be gained by subtracting it
from
| another time ... My scenario is that the employee starts at 5 pm on say a
| Monday, and finishes 10:00 am the following morning, on the timesheet I
enter
| the 5 to midnight on the Monday, and then the Midnight to 10:00 on Tuesday
(I
| need for the midnight to be visible), hence my disapperance.
|
| Thanks anyway Jim, all information is very much appreciated.
|
| "Jim Rech" wrote:
|
| > >>I need midnight to remain as a figure as I need it to calculate the
| > >>difference.
| >
| > You could enter a time very near to midnight, such as 12:00:00.001 AM.
| >
| > Of course, since midnight is zero there is nothing to be gained by
| > subtracting it from another time.
| >
| > --
| > Jim Rech
| > Excel MVP
| >
| > message | > |I am using Excel 2002 - and working with times. I have formatted the
times
| > | to hh:mm AM/PM and when I enter 0:00 to get 12:00 AM it works good.
But
| > when
| > | I hide the zeros the midnight time disappears. I need midnight to
remain
| > as
| > | a figure as I need it to calculate the difference.
| > |
| > | Example
| > | A1 = 12:00 AM
| > | A2 = 10:00 AM
| > | A3 = 10:00 hour working day
| > |
| > | If I have the zeros showing it makes for a very busy timesheet.
| > |
| > | Anyone got any suggestions. Thanks to all who help.
| >
| >
| >
Click to expand...
 
S

Shirley Zaknich

If this appears twice, I apologise, but I had a feeling the first time I sent
this to you, the text had disappeared.

Thanks Jim once again for your response. It's great to get so much feedback
as I have indicated to Sandy and Eli... and interesting feedback. Work with
times and dates on a timesheet in a roster system is new to me and I have
learned so much from you all.

My main concern was the midnight disappearing after I remove the zero's from
sight, therefore I did not say that on the previous day the person did start
at 5:00pm, but finished at 10:00 AM the next day.

For example, A1 = 5:00 PM
A2 = Midnight or 12:00 AM

B1 = Midnight or 12:00 (The zero factor... 0:00 AM which hides)
B2 = 10:00 AM

I always got the right hours calculated, but being a timesheet, I wanted the
0:00 AM to be visible to the person, and not left as a blank cell.

Your explanation back to me, was very clear, therefore, you must be somewhat
of a guru in Excel. Look out Jim, I plan to be one an MVP but on Word.
Cheers

Thanking you again.
 
S

Shirley Zaknich

Thanks Eli for your response

My main concern was the midnight disappearing after I remove the zero's from
sight, therefore I did not say that on the previous day the person did start
at 5:00pm, but finished at 10:00 AM the next day.

For example, A1 = 5:00 PM
A2 = Midnight or 12:00 AM

B1 = Midnight or 12:00 (The zero factor... 0:00 AM which hides)
B2 = 10:00 AM

I always got the right hours calculated, but being a timesheet, I wanted the
0:00 AM to be visible to the person, and not left as a blank cell.

Like you I am not one of the experts and somewhat new to the forum. I hope
the above explains in detail what I was trying to do. I hide the all zeros
on the timesheet by going into Tools/Options/Veiw. The link you gave me, I
found very interesting.

Thanking you again.
 
Top