| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
get HOUR check from NOW() cell
hi, not good with date, times, cannot get out of help, want to test for cell
is x hours past cell that has been updated with NOW(). e.g.: cell M9 with NOW(): 2008-05-27 2:00:00 AM =IF(current time is 2 hoursM9,0,1) would suggest help file have example how to manipulate NOW(). trying: =TRIM(LEFT(M9,SEARCH(" ",M9,12))) gets a value error/ might make to big a formula. would think easier way to manipulate this data. thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
get HOUR check from NOW() cell
after some effort, came up with: (is there a shorter/ simpler way)
=IF((HOUR(TRIM(RIGHT(M9,11)))+1)HOUR(NOW()),1,0) "Nastech" wrote: hi, not good with date, times, cannot get out of help, want to test for cell is x hours past cell that has been updated with NOW(). e.g.: cell M9 with NOW(): 2008-05-27 2:00:00 AM =IF(current time is 2 hoursM9,0,1) would suggest help file have example how to manipulate NOW(). trying: =TRIM(LEFT(M9,SEARCH(" ",M9,12))) gets a value error/ might make to big a formula. would think easier way to manipulate this data. thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
get HOUR check from NOW() cell
but last example seems can be off by 59 minutes, (is 1 from 1:00 to 1:59?)
"Nastech" wrote: after some effort, came up with: (is there a shorter/ simpler way) =IF((HOUR(TRIM(RIGHT(M9,11)))+1)HOUR(NOW()),1,0) "Nastech" wrote: hi, not good with date, times, cannot get out of help, want to test for cell is x hours past cell that has been updated with NOW(). e.g.: cell M9 with NOW(): 2008-05-27 2:00:00 AM =IF(current time is 2 hoursM9,0,1) would suggest help file have example how to manipulate NOW(). trying: =TRIM(LEFT(M9,SEARCH(" ",M9,12))) gets a value error/ might make to big a formula. would think easier way to manipulate this data. thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
get HOUR check from NOW() cell
example need to work getting longer.. must be a shorter way:
IF(OR(IF(M90,(HOUR(TRIM(RIGHT(M9,11)))+$C$6)<HOUR (NOW()),TRUE)),0,1) still off by 59 mins.. "Nastech" wrote: but last example seems can be off by 59 minutes, (is 1 from 1:00 to 1:59?) "Nastech" wrote: after some effort, came up with: (is there a shorter/ simpler way) =IF((HOUR(TRIM(RIGHT(M9,11)))+1)HOUR(NOW()),1,0) "Nastech" wrote: hi, not good with date, times, cannot get out of help, want to test for cell is x hours past cell that has been updated with NOW(). e.g.: cell M9 with NOW(): 2008-05-27 2:00:00 AM =IF(current time is 2 hoursM9,0,1) would suggest help file have example how to manipulate NOW(). trying: =TRIM(LEFT(M9,SEARCH(" ",M9,12))) gets a value error/ might make to big a formula. would think easier way to manipulate this data. thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
get HOUR check from NOW() cell
Give this formula a try...
=IF(24*(NOW()-M9)2,1,0) Rick "Nastech" wrote in message ... example need to work getting longer.. must be a shorter way: IF(OR(IF(M90,(HOUR(TRIM(RIGHT(M9,11)))+$C$6)<HOUR (NOW()),TRUE)),0,1) still off by 59 mins.. "Nastech" wrote: but last example seems can be off by 59 minutes, (is 1 from 1:00 to 1:59?) "Nastech" wrote: after some effort, came up with: (is there a shorter/ simpler way) =IF((HOUR(TRIM(RIGHT(M9,11)))+1)HOUR(NOW()),1,0) "Nastech" wrote: hi, not good with date, times, cannot get out of help, want to test for cell is x hours past cell that has been updated with NOW(). e.g.: cell M9 with NOW(): 2008-05-27 2:00:00 AM =IF(current time is 2 hoursM9,0,1) would suggest help file have example how to manipulate NOW(). trying: =TRIM(LEFT(M9,SEARCH(" ",M9,12))) gets a value error/ might make to big a formula. would think easier way to manipulate this data. thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
get HOUR check from NOW() cell
thanks! works great for minutes away. only difference is that am using 0 as
null/false condition.. or =IF(24*(NOW()-M9)2,0,1) in example if date present in m9 =IF(OR(IF(M90,24*(NOW()-M9)$M$6,TRUE)),0,1) think tried to get this couple times, way back, never could get answer/ didn't know how to ask, was a major problem. could not figure from now() help. not much there. thanks again. "Rick Rothstein (MVP - VB)" wrote: Give this formula a try... =IF(24*(NOW()-M9)2,1,0) Rick "Nastech" wrote in message ... example need to work getting longer.. must be a shorter way: IF(OR(IF(M90,(HOUR(TRIM(RIGHT(M9,11)))+$C$6)<HOUR (NOW()),TRUE)),0,1) still off by 59 mins.. "Nastech" wrote: but last example seems can be off by 59 minutes, (is 1 from 1:00 to 1:59?) "Nastech" wrote: after some effort, came up with: (is there a shorter/ simpler way) =IF((HOUR(TRIM(RIGHT(M9,11)))+1)HOUR(NOW()),1,0) "Nastech" wrote: hi, not good with date, times, cannot get out of help, want to test for cell is x hours past cell that has been updated with NOW(). e.g.: cell M9 with NOW(): 2008-05-27 2:00:00 AM =IF(current time is 2 hoursM9,0,1) would suggest help file have example how to manipulate NOW(). trying: =TRIM(LEFT(M9,SEARCH(" ",M9,12))) gets a value error/ might make to big a formula. would think easier way to manipulate this data. thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I subtract 1 hour from one cell and input it another? | Excel Worksheet Functions | |||
| Cannot check the check mark box in cell | Excel Worksheet Functions | |||
| Check if Conditional Format is True or False / Check cell Color | Excel Worksheet Functions | |||
| convert decimal numbers to a fraction of an hour for payroll hour | Excel Worksheet Functions | |||
| How can I round an hour to the nearest 1/4 hour? | Excel Worksheet Functions | |||
Linear Mode
