AM-PM

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?
 
N

Norman Harker

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.
 
G

Guest

.... 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.
 
D

David McRitchie

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.
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AM - PM 4
Extracting just the time portion 5
Excel Catagorise AM or PM 7
INDEX MATCH SMALL 12
Subtracting time...again 2
Converting hours to minutes 5
Excel Facetime Measurement 1
If time Go to Function 1

Top