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

Shirley Zaknich

.... 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. .
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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

Shirley Zaknich

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. .
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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

Shirley Zaknich

You beauty!! Your article is also ver interesting. Thanks again
----- David McRitchie wrote: ----

Hi Shirley
A1` contains start time of 4:00 p
B1: contains end time of 7:00 a
subtract A1 from B1 and add 24 hours (24hrs = 1 day

=B1-A1+(A1>B1

See my page on Date and Tim
http://www.mvps.org/dmcritchie/excel/datetime.ht
 

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

Extracting just the time portion 6
Subtracting time...again 2
INDEX MATCH SMALL 13
Time Zone Conversion Table 1
AM - PM 4
Excel Facetime Measurement 1
Min, Max 3
VBA Export to PDF 0

Top