Time Errors
 

Hi Benwin
one way:
=IF(Front!B90,TEXT(Front!B9,"hh:mm") & "-" &
TEXT(Front!C9,"hh:mm"),"")
this will display the time in the format 00:00 - 23:59
If you would like the am/pm notation (without adding these characters)
try
=IF(Front!B90,TEXT(MOD(Front!B9,0.5),"hh:mm") & "-" &
TEXT(MOD(Front!C9,0.5),"hh:mm"),"")

Frank


Benwin wrote:
Hello, I was wondering if anyone can help me out. I am creating a
form
to help in scheduling staff. Right now my problem is that whenever I
enter 1:30am as a time and then copy that cell's contents to another
page it copies as 1.5. To keep things simple for the staff it works
like this, I enter everybodys start time and end time on one page,
then on the printable page it checks to see if there is a start time,
if there is it prints [Start time]-[End time].

Here is the equation that I am using, I don't really know that much
about macros or anything like that so it's fairly complicated.


=IF(MAX(Front!B9)=0,"",IF(Front!B9*24=12,Front!B9 *24-12,Front!B9*24)&"
-"&IF(Front!C9*24=12,Front!C9*24-12,Front!C9*24))

So if I enter 10:30am for the start time, and 7:00pm for the end time
it diplays "10:30-7", but for 5:00pm as the start and 1:30am as the
end it displays "5-1.5"

Any help would be appreciated.


Benwin
---------------------------------------------------------------------
---
Posted via http://www.mcse.ms
---------------------------------------------------------------------
---
View this thread: http://www.mcse.ms/message377075.html

Time Errors
 

Hello, I was wondering if anyone can help me out. I am creating a for
to help in scheduling staff. Right now my problem is that whenever
enter 1:30am as a time and then copy that cell's contents to anothe
page it copies as 1.5. To keep things simple for the staff it work
like this, I enter everybodys start time and end time on one page, the
on the printable page it checks to see if there is a start time, i
there is it prints [Start time]-[End time].

Here is the equation that I am using, I don't really know that muc
about macros or anything like that so it's fairly complicated.

=IF(MAX(Front!B9)=0,"",IF(Front!B9*24=12,Front!B9 *24-12,Front!B9*24)&"-"&IF(Front!C9*24=12,Front!C9*24-12,Front!C9*24))

So if I enter 10:30am for the start time, and 7:00pm for the end tim
it diplays "10:30-7", but for 5:00pm as the start and 1:30am as the en
it displays "5-1.5"

Any help would be appreciated

Benwi
-----------------------------------------------------------------------
Posted via http://www.mcse.m
-----------------------------------------------------------------------
View this thread: http://www.mcse.ms/message377075.htm

Time Errors
 

Just curious Frank
=IF(Front!B90,TEXT(MOD(Front!B9,0.5),"hh:mm") & "-"
&TEXT(MOD(Front!C9,0.5),"hh:mm"),"")

doesn't produce am/pm notation for me - xl97 SR2 US English - why do you
think it would do that? If it does it for you, perhaps it has to do with
your regional settings.

--
Regards,
Tom Ogilvy



Frank Kabel wrote in message
...
Hi Benwin
one way:
=IF(Front!B90,TEXT(Front!B9,"hh:mm") & "-" &
TEXT(Front!C9,"hh:mm"),"")
this will display the time in the format 00:00 - 23:59
If you would like the am/pm notation (without adding these characters)
try
=IF(Front!B90,TEXT(MOD(Front!B9,0.5),"hh:mm") & "-" &
TEXT(MOD(Front!C9,0.5),"hh:mm"),"")

Frank


Benwin wrote:
Hello, I was wondering if anyone can help me out. I am creating a
form
to help in scheduling staff. Right now my problem is that whenever I
enter 1:30am as a time and then copy that cell's contents to another
page it copies as 1.5. To keep things simple for the staff it works
like this, I enter everybodys start time and end time on one page,
then on the printable page it checks to see if there is a start time,
if there is it prints [Start time]-[End time].

Here is the equation that I am using, I don't really know that much
about macros or anything like that so it's fairly complicated.


=IF(MAX(Front!B9)=0,"",IF(Front!B9*24=12,Front!B9 *24-12,Front!B9*24)&"
-"&IF(Front!C9*24=12,Front!C9*24-12,Front!C9*24))

So if I enter 10:30am for the start time, and 7:00pm for the end time
it diplays "10:30-7", but for 5:00pm as the start and 1:30am as the
end it displays "5-1.5"

Any help would be appreciated.


Benwin
---------------------------------------------------------------------
---
Posted via http://www.mcse.ms
---------------------------------------------------------------------
---
View this thread: http://www.mcse.ms/message377075.html

Time Errors
 

Tom Ogilvy wrote:
Just curious Frank
=IF(Front!B90,TEXT(MOD(Front!B9,0.5),"hh:mm") & "-"
&TEXT(MOD(Front!C9,0.5),"hh:mm"),"")

doesn't produce am/pm notation for me - xl97 SR2 US English - why
do you think it would do that? If it does it for you, perhaps it has
to do with your regional settings.

Hi Tom
That is what I was trying to say in my post :-)
This formula will produce times ranging from 00:00 - 11:59. So for
example converting the time 23:00 to 11:00 (a kind of am/pm notation
without trailing 'am/pm' text). I choosed this formula based on the
OP's example formula (which subtracted 12 from the time*24 if 12) -
therefore making some assumption about his requierements

Best regards
Frank

Time Errors
 

OK. I thought you were saying it would produce am/pm notation. ex: 11:50
am without typing in the am/pm in the format string. Clever.


--
Regards,
Tom Ogilvy

Frank Kabel wrote in message
...
Tom Ogilvy wrote:
Just curious Frank
=IF(Front!B90,TEXT(MOD(Front!B9,0.5),"hh:mm") & "-"
&TEXT(MOD(Front!C9,0.5),"hh:mm"),"")

doesn't produce am/pm notation for me - xl97 SR2 US English - why
do you think it would do that? If it does it for you, perhaps it has
to do with your regional settings.

Hi Tom
That is what I was trying to say in my post :-)
This formula will produce times ranging from 00:00 - 11:59. So for
example converting the time 23:00 to 11:00 (a kind of am/pm notation
without trailing 'am/pm' text). I choosed this formula based on the
OP's example formula (which subtracted 12 from the time*24 if 12) -
therefore making some assumption about his requierements

Best regards
Frank