AM-PM

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 befo

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+(B1A1)
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+(B1A1))*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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"jason b" wrote in message
...
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?

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

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+(B1A1)
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+(B1A1))*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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"jason b" wrote in message
...
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 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" wrote in message ...
... 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 befo

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+(B1A1)
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+(B1A1))*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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"jason b" wrote in message
...
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?

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" wrote in message ...
... 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 befo
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+(B1A1)
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+(B1A1))*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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"jason b" wrote in message
...
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 Shirley,
A1` contains start time of 4:00 pm
B1: contains end time of 7:00 am
subtract A1 from B1 and add 24 hours (24hrs = 1 day)

=B1-A1+(A1B1)

See my page on Date and Time
http://www.mvps.org/dmcritchie/excel/datetime.htm
---
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" wrote ..
... 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.

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

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

=B1-A1+(A1B1)

See my page on Date and Time
http://www.mvps.org/dmcritchie/excel/datetime.htm
---
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" wrote ..
... 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.