AM-PM

  • Thread starter Thread starter jason b
  • Start date Start date
J

jason b

I am using excel and am tring to find out me sleeping
habites. Is there a "fx" in Excel that will tell the
diff. between 11:50 PM to 7:00 AM in numbers. If not how
can i do this?
 
Hi Jason!

No need to post to more than one group and you should try and keep to
the same thread for the same question.

Here's the reply given before:

Frank and the link provided by Niek give you the answer.

Here it is explained step by step:

In A1 put:
23:50
In B1 put:
7:00
In C1 put:
=B1-A1+(B1<A1)
Returns 7:10

Time is recorded by Excel as a decimal part of 1 day. If you have
spanned Midnight, the second time in most uses will be an earlier
number than the first one. We make use of that by adding 1 if it is an
earlier number.

If you want that as decimal hours you can use:

=(B1-A1+(B1<A1))*24
Returns 7.166667

An alternative approach is to put the date and time in your entries
and in that case the simple solution would then be =B1-A1


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
.... but what if you want to know the difference between 24:00 and 7:00 AM. the 24:00 formats itself as 00:00. How can I stop it doing this? And it always won't be in the same cell. The answer is OK, it's just the zeroing off of the 24. Worksheet is a timesheet of employee times.

----- Norman Harker wrote: -----

Hi Jason!

No need to post to more than one group and you should try and keep to
the same thread for the same question.

Here's the reply given before:

Frank and the link provided by Niek give you the answer.

Here it is explained step by step:

In A1 put:
23:50
In B1 put:
7:00
In C1 put:
=B1-A1+(B1<A1)
Returns 7:10

Time is recorded by Excel as a decimal part of 1 day. If you have
spanned Midnight, the second time in most uses will be an earlier
number than the first one. We make use of that by adding 1 if it is an
earlier number.

If you want that as decimal hours you can use:

=(B1-A1+(B1<A1))*24
Returns 7.166667

An alternative approach is to put the date and time in your entries
and in that case the simple solution would then be =B1-A1


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Shirley,
Are you talking about displaying a time or displaying a SUM.

For a SUM format as [h]:mm to keep the hours from rolling
over into days.

If you are talking about formatting midnight as 24:00 forget it.
Computers have changed the world 24:00 hours is now 0:00
hours . And references to AM or PM with noon or midnight while
incorrect are now due to computers midnight 0:00 AM or 12:00AM,
and noon is 12:00 PM. .
--


Shirley Zaknich said:
... but what if you want to know the difference between 24:00 and 7:00 AM. the 24:00 formats itself as 00:00. How can I stop it
Click to expand...
doing this? And it always won't be in the same cell. The answer is OK, it's just the zeroing off of the 24. Worksheet is a
timesheet of employee times.
 
I have a start time and a finish time. For example, start at 4:00 pm and finish 7:00 am next day. It's part of a sleep shift. I want to know how many hours this person worked. Yeh! I have already worked out about the 0:00. Thanks for your help.

----- David McRitchie wrote: -----

Hi Shirley,
Are you talking about displaying a time or displaying a SUM.

For a SUM format as [h]:mm to keep the hours from rolling
over into days.

If you are talking about formatting midnight as 24:00 forget it.
Computers have changed the world 24:00 hours is now 0:00
hours . And references to AM or PM with noon or midnight while
incorrect are now due to computers midnight 0:00 AM or 12:00AM,
and noon is 12:00 PM. .
--


Shirley Zaknich said:
... but what if you want to know the difference between 24:00 and 7:00 AM. the 24:00 formats itself as 00:00. How can I stop it
Click to expand...
doing this? And it always won't be in the same cell. The answer is OK, it's just the zeroing off of the 24. Worksheet is a
timesheet of employee times.
 
Back
Top