Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 153
Default Greater than/Less than a certain Time (revisited)

Hello,

I posted a question a couple days ago, and although it seemed that
things were working ok, they weren't. I've got a date/time stamp IN
in column A, and a date/time stamp OUT in column B. I need column C
to say Yes if the time OUT is less than 3:30 PM, and NO if the time
out is after 3:30 PM. The format in both the IN and the OUT columns
is 'mm/dd/yy h:mm AM/PM' (e.g., 11/24/06 9:39 AM). Below are the two
formulas that I have tried:

=IF(J2="","",IF(J2<="3:30 PM","Yes","No"))
=IF(J2="","",IF(J2<TIMEVALUE("3:30 PM"),"yes","no"))

What adjustments do I need to make to this? Thanks for any help.

Frank
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Greater than/Less than a certain Time (revisited)

Try this:

=IF(J2="","",IF(MOD(J2,1)<TIME(15,30,0),"Yes","No" ))

Biff

"Phrank" wrote in message
...
Hello,

I posted a question a couple days ago, and although it seemed that
things were working ok, they weren't. I've got a date/time stamp IN
in column A, and a date/time stamp OUT in column B. I need column C
to say Yes if the time OUT is less than 3:30 PM, and NO if the time
out is after 3:30 PM. The format in both the IN and the OUT columns
is 'mm/dd/yy h:mm AM/PM' (e.g., 11/24/06 9:39 AM). Below are the two
formulas that I have tried:

=IF(J2="","",IF(J2<="3:30 PM","Yes","No"))
=IF(J2="","",IF(J2<TIMEVALUE("3:30 PM"),"yes","no"))

What adjustments do I need to make to this? Thanks for any help.

Frank



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 153
Default Greater than/Less than a certain Time (revisited)

Beautiful! Works great. Thanks Biff!!!

Frank

On Sat, 25 Nov 2006 00:01:04 -0500, "T. Valko"
wrote:

Try this:

=IF(J2="","",IF(MOD(J2,1)<TIME(15,30,0),"Yes","No "))

Biff

"Phrank" wrote in message
.. .
Hello,

I posted a question a couple days ago, and although it seemed that
things were working ok, they weren't. I've got a date/time stamp IN
in column A, and a date/time stamp OUT in column B. I need column C
to say Yes if the time OUT is less than 3:30 PM, and NO if the time
out is after 3:30 PM. The format in both the IN and the OUT columns
is 'mm/dd/yy h:mm AM/PM' (e.g., 11/24/06 9:39 AM). Below are the two
formulas that I have tried:

=IF(J2="","",IF(J2<="3:30 PM","Yes","No"))
=IF(J2="","",IF(J2<TIMEVALUE("3:30 PM"),"yes","no"))

What adjustments do I need to make to this? Thanks for any help.

Frank


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Greater than/Less than a certain Time (revisited)

You're welcome. Thanks for the feedback!

Biff

"Phrank" wrote in message
...
Beautiful! Works great. Thanks Biff!!!

Frank

On Sat, 25 Nov 2006 00:01:04 -0500, "T. Valko"
wrote:

Try this:

=IF(J2="","",IF(MOD(J2,1)<TIME(15,30,0),"Yes","N o"))

Biff

"Phrank" wrote in message
. ..
Hello,

I posted a question a couple days ago, and although it seemed that
things were working ok, they weren't. I've got a date/time stamp IN
in column A, and a date/time stamp OUT in column B. I need column C
to say Yes if the time OUT is less than 3:30 PM, and NO if the time
out is after 3:30 PM. The format in both the IN and the OUT columns
is 'mm/dd/yy h:mm AM/PM' (e.g., 11/24/06 9:39 AM). Below are the two
formulas that I have tried:

=IF(J2="","",IF(J2<="3:30 PM","Yes","No"))
=IF(J2="","",IF(J2<TIMEVALUE("3:30 PM"),"yes","no"))

What adjustments do I need to make to this? Thanks for any help.

Frank




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 153
Default Greater than/Less than a certain Time (revisited)

Hi again,

As I said, this works great. However, it does focus only on the
time, regardless of the day. What would I need to add to also have it
look at the day? For example, if someone turned something IN on one
day, and it wasn't returned (OUT) until the next day, I need it to
return 'No' also. As it is right now, as long as it is before 3:30,
it doesn't matter if the OUT date is a month after the item was turned
IN. Thanks for any additional help.

Frank

On Sat, 25 Nov 2006 00:01:04 -0500, "T. Valko"
wrote:

Try this:

=IF(J2="","",IF(MOD(J2,1)<TIME(15,30,0),"Yes","No "))

Biff

"Phrank" wrote in message
.. .
Hello,

I posted a question a couple days ago, and although it seemed that
things were working ok, they weren't. I've got a date/time stamp IN
in column A, and a date/time stamp OUT in column B. I need column C
to say Yes if the time OUT is less than 3:30 PM, and NO if the time
out is after 3:30 PM. The format in both the IN and the OUT columns
is 'mm/dd/yy h:mm AM/PM' (e.g., 11/24/06 9:39 AM). Below are the two
formulas that I have tried:

=IF(J2="","",IF(J2<="3:30 PM","Yes","No"))
=IF(J2="","",IF(J2<TIMEVALUE("3:30 PM"),"yes","no"))

What adjustments do I need to make to this? Thanks for any help.

Frank




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 153
Default Greater than/Less than a certain Time (revisited)

Hi again,

I found a way to do this. Instead of having one column for the IN and
the OUT with a date/time stamp, I split each of these into two
columns, one with date and the other with time. Then, in the column
that asks if the item was returned (OUT) before 3:30, I put this
formula:

=IF(L7="","",IF(AND(DATEDIF(A7,B7,"D")=0,MOD(C7,1) <TIME(15,30,0)),"Yes","No"))

It works great. Thanks again for looking and with your help.

Frank
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hot key for time? Dave in Des Moines New Users to Excel 2 March 24th 06 05:31 PM
Hot key for time? Dave in Des Moines Excel Worksheet Functions 2 March 24th 06 05:15 PM
Hot key for time? Dave in Des Moines Excel Discussion (Misc queries) 1 March 24th 06 04:46 PM
Help with SUMIF, INDEX, LOOKUP Please !! Robert Excel Worksheet Functions 13 March 13th 05 01:17 AM
count TIME that is GREATER than 24 hours JonPilot Excel Worksheet Functions 1 January 31st 05 11:55 PM


All times are GMT +1. The time now is 09:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"