Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.


--
Randy Street
Rancho Cucamonga, CA

=N(MOD(F3-E3,1)*24)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Randy" wrote in message
...
Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to
be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.


--
Randy Street
Rancho Cucamonga, CA

=(B2-A2)*24

formatted as general




--


Regards,


Peo Sjoblom


"Randy" wrote in message
...
Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to
be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.


--
Randy Street
Rancho Cucamonga, CA

Assumed that start time is in A1, end time is in B1, and that both are
formatted as time hh:mm:ss PM/AM. Then in C1
=IF(B1<A1,1-ABS(B1-A1),B1-A1)

Now for the format trick, choose C1 and Format | Cells, go to custom format
and type in [h]
That should do it for you.

As for why 12:00 AM isn't showing up, not certain - depends on format of the
cell holding it. 12:00:00 AM has a value of zero, and that may have
something to do with it.


"Randy" wrote:

Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.


--
Randy Street
Rancho Cucamonga, CA

Hi Randy:

1. always remember to put a single space between the time and the am/pm when
typing data. So type:

11:00 am
and not:
11:00am

2. In A1 and B1 enter:
11:00 PM 1:00 AM
and in C1 enter:

=IF(B1<A1,B1+1-A1,B1-A1)

click on C1 and:

Format Cells... Number Custom h

and you will see the 2

You need this type of formula in C1 if you start before midnight and end
after midnight.

--
Gary''s Student - gsnu200784


"Randy" wrote:

Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.


--
Randy Street
Rancho Cucamonga, CA

Thank you, thank you, thank you! Worked like a charm! YOU ROCK!
--
Randy Street
Rancho Cucamonga, CA


"JLatham" wrote:

Assumed that start time is in A1, end time is in B1, and that both are
formatted as time hh:mm:ss PM/AM. Then in C1
=IF(B1<A1,1-ABS(B1-A1),B1-A1)

Now for the format trick, choose C1 and Format | Cells, go to custom format
and type in [h]
That should do it for you.

As for why 12:00 AM isn't showing up, not certain - depends on format of the
cell holding it. 12:00:00 AM has a value of zero, and that may have
something to do with it.


"Randy" wrote:

Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.


--
Randy Street
Rancho Cucamonga, CA

Glad I ended up in line first. Thanks for the feedback.

You might check out Bob Phillips, Peo Sjoblom and Gary"s Student replies as
they all offer something and you may find you prefer one of their solutions
to the one I offered.

"Randy" wrote:

Thank you, thank you, thank you! Worked like a charm! YOU ROCK!
--
Randy Street
Rancho Cucamonga, CA


"JLatham" wrote:

Assumed that start time is in A1, end time is in B1, and that both are
formatted as time hh:mm:ss PM/AM. Then in C1
=IF(B1<A1,1-ABS(B1-A1),B1-A1)

Now for the format trick, choose C1 and Format | Cells, go to custom format
and type in [h]
That should do it for you.

As for why 12:00 AM isn't showing up, not certain - depends on format of the
cell holding it. 12:00:00 AM has a value of zero, and that may have
something to do with it.


"Randy" wrote:

Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.


--
Randy Street
Rancho Cucamonga, CA

Hi J...you are welcome for the feedback...I have another questions for
ya....How can I incorporate something to the effect that if the value of your
formula =IF(B1<A1,1-ABS(B1-A1),B1-A1) is less than 2:00, to set the value at
2:00?
--
Randy Street
Rancho Cucamonga, CA


"JLatham" wrote:

Glad I ended up in line first. Thanks for the feedback.

You might check out Bob Phillips, Peo Sjoblom and Gary"s Student replies as
they all offer something and you may find you prefer one of their solutions
to the one I offered.

"Randy" wrote:

Thank you, thank you, thank you! Worked like a charm! YOU ROCK!
--
Randy Street
Rancho Cucamonga, CA


"JLatham" wrote:

Assumed that start time is in A1, end time is in B1, and that both are
formatted as time hh:mm:ss PM/AM. Then in C1
=IF(B1<A1,1-ABS(B1-A1),B1-A1)

Now for the format trick, choose C1 and Format | Cells, go to custom format
and type in [h]
That should do it for you.

As for why 12:00 AM isn't showing up, not certain - depends on format of the
cell holding it. 12:00:00 AM has a value of zero, and that may have
something to do with it.


"Randy" wrote:

Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.


--
Randy Street
Rancho Cucamonga, CA

I think if you'll just wrap all of that up in a MAX() function, it'll do the
trick:

=MAX(2/24, IF(B1<A1,1-ABS(B1-A1),B1-A1))
So if the result is less than 2, it will display as 2, while if the result
is greater than 2 hours, it'll show that.

You can change 2/24 to 1/12 (same value, just simplified). I used 2/24
because it more clearly shows that you're looking for a value that is 2 of 24
hours.

Using that formula, your case of 10 pm - 11 pm will show up as 2 instead of 1.


"Randy" wrote:

Hi J...you are welcome for the feedback...I have another questions for
ya....How can I incorporate something to the effect that if the value of your
formula =IF(B1<A1,1-ABS(B1-A1),B1-A1) is less than 2:00, to set the value at
2:00?
--
Randy Street
Rancho Cucamonga, CA


"JLatham" wrote:

Glad I ended up in line first. Thanks for the feedback.

You might check out Bob Phillips, Peo Sjoblom and Gary"s Student replies as
they all offer something and you may find you prefer one of their solutions
to the one I offered.

"Randy" wrote:

Thank you, thank you, thank you! Worked like a charm! YOU ROCK!
--
Randy Street
Rancho Cucamonga, CA


"JLatham" wrote:

Assumed that start time is in A1, end time is in B1, and that both are
formatted as time hh:mm:ss PM/AM. Then in C1
=IF(B1<A1,1-ABS(B1-A1),B1-A1)

Now for the format trick, choose C1 and Format | Cells, go to custom format
and type in [h]
That should do it for you.

As for why 12:00 AM isn't showing up, not certain - depends on format of the
cell holding it. 12:00:00 AM has a value of zero, and that may have
something to do with it.


"Randy" wrote:

Anyone know how I can get these scenarios to calculate? I need to leave in
this format but need to calculate how much time, which in this case is 2
hours. I just need it to state 2, not the "hours" parts. I also need if to be
able to tell the difference between the two scenarios as either one may
entered in.

11:00 PM to 1:00 AM = 2
10:00 PM to 11:00 PM = 1

Also one last question....how come when 12:00 AM is enetered is does not
appear? Calulations still calculate but you can't see the 12:00 AM part?

Any assistance anyone can provide will be greatly appreciated. I thank you
in adavance for your time.


--
Randy Street
Rancho Cucamonga, CA