Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#N/A errors when matching time values | Excel Worksheet Functions | |||
Run time errors | Excel Discussion (Misc queries) | |||
Excel Run Time Errors | Excel Discussion (Misc queries) | |||
Avoiding Run-Time errors when sending emails from Excel | Excel Programming | |||
Run-time errors | Excel Programming |