| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Time - By 8am the next morning
I have two columns with date and times. I need a formula to show that the
date in the second column was no later than 8am the day after the date/time in the first column. For example: A1 04/05/09 14:35 PM B1 04/06/09 11:01 AM So using my explanation above, this would be "not compliant" with the scenario and I would like an "N" returned. A compliant example would be: A1 04/06/09 14:35 PM B1 04/07/09 07:30 AM Is there a formula for this? Thanks!!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time - By 8am the next morning
Are you sure your dates/times are true Excel dates/times? Excel doesn't
recognize those as true dates/times when you have the AM/PM following a 24hr time format: 14:35 PM This would be a true Excel date/time: 04/05/09 2:35 PM One way to test is to see if the date/time is a number. In Excel, true dates/times are really just numbers formatted to look like dates/times. Let's assume cell A1 contains 04/05/09 14:35 PM. What result do you get from this formula: =ISNUMBER(A1) If you get a result of FALSE then your dates/times are not true Excel dates/times. They are TEXT strings. -- Biff Microsoft Excel MVP "Jackajoo" wrote in message ... I have two columns with date and times. I need a formula to show that the date in the second column was no later than 8am the day after the date/time in the first column. For example: A1 04/05/09 14:35 PM B1 04/06/09 11:01 AM So using my explanation above, this would be "not compliant" with the scenario and I would like an "N" returned. A compliant example would be: A1 04/06/09 14:35 PM B1 04/07/09 07:30 AM Is there a formula for this? Thanks!!! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time - By 8am the next morning
Hi!
I did what you asked and I got a "TRUE" answer. It just depends on where you are viewing the field. If you look at the actual cell, it has "Army Time" (using the 24 hour clock) and if you look at the top (the formula bar), it is in what I call "regular time" (using the 12 hour clock). Let me know if you can help! I appreciate it! "T. Valko" wrote: Are you sure your dates/times are true Excel dates/times? Excel doesn't recognize those as true dates/times when you have the AM/PM following a 24hr time format: 14:35 PM This would be a true Excel date/time: 04/05/09 2:35 PM One way to test is to see if the date/time is a number. In Excel, true dates/times are really just numbers formatted to look like dates/times. Let's assume cell A1 contains 04/05/09 14:35 PM. What result do you get from this formula: =ISNUMBER(A1) If you get a result of FALSE then your dates/times are not true Excel dates/times. They are TEXT strings. -- Biff Microsoft Excel MVP "Jackajoo" wrote in message ... I have two columns with date and times. I need a formula to show that the date in the second column was no later than 8am the day after the date/time in the first column. For example: A1 04/05/09 14:35 PM B1 04/06/09 11:01 AM So using my explanation above, this would be "not compliant" with the scenario and I would like an "N" returned. A compliant example would be: A1 04/06/09 14:35 PM B1 04/07/09 07:30 AM Is there a formula for this? Thanks!!! . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time - By 8am the next morning
Try this...
Assuming that the date/time in B1 will *always* be later than the date/time in A1. =IF(COUNT(A1:B1)=2,IF(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"") If there will *always* be entries in both cells then you can shorten that to: =IF(B1INT(A1+1)+TIME(8,0,0),"N","") -- Biff Microsoft Excel MVP "Jackajoo" wrote in message ... Hi! I did what you asked and I got a "TRUE" answer. It just depends on where you are viewing the field. If you look at the actual cell, it has "Army Time" (using the 24 hour clock) and if you look at the top (the formula bar), it is in what I call "regular time" (using the 12 hour clock). Let me know if you can help! I appreciate it! "T. Valko" wrote: Are you sure your dates/times are true Excel dates/times? Excel doesn't recognize those as true dates/times when you have the AM/PM following a 24hr time format: 14:35 PM This would be a true Excel date/time: 04/05/09 2:35 PM One way to test is to see if the date/time is a number. In Excel, true dates/times are really just numbers formatted to look like dates/times. Let's assume cell A1 contains 04/05/09 14:35 PM. What result do you get from this formula: =ISNUMBER(A1) If you get a result of FALSE then your dates/times are not true Excel dates/times. They are TEXT strings. -- Biff Microsoft Excel MVP "Jackajoo" wrote in message ... I have two columns with date and times. I need a formula to show that the date in the second column was no later than 8am the day after the date/time in the first column. For example: A1 04/05/09 14:35 PM B1 04/06/09 11:01 AM So using my explanation above, this would be "not compliant" with the scenario and I would like an "N" returned. A compliant example would be: A1 04/06/09 14:35 PM B1 04/07/09 07:30 AM Is there a formula for this? Thanks!!! . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time - By 8am the next morning
This is great - however - is there any way I can exclude weekends? I just
copiy andpasted the formula and it worked!!! "T. Valko" wrote: Try this... Assuming that the date/time in B1 will *always* be later than the date/time in A1. =IF(COUNT(A1:B1)=2,IF(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"") If there will *always* be entries in both cells then you can shorten that to: =IF(B1INT(A1+1)+TIME(8,0,0),"N","") -- Biff Microsoft Excel MVP "Jackajoo" wrote in message ... Hi! I did what you asked and I got a "TRUE" answer. It just depends on where you are viewing the field. If you look at the actual cell, it has "Army Time" (using the 24 hour clock) and if you look at the top (the formula bar), it is in what I call "regular time" (using the 12 hour clock). Let me know if you can help! I appreciate it! "T. Valko" wrote: Are you sure your dates/times are true Excel dates/times? Excel doesn't recognize those as true dates/times when you have the AM/PM following a 24hr time format: 14:35 PM This would be a true Excel date/time: 04/05/09 2:35 PM One way to test is to see if the date/time is a number. In Excel, true dates/times are really just numbers formatted to look like dates/times. Let's assume cell A1 contains 04/05/09 14:35 PM. What result do you get from this formula: =ISNUMBER(A1) If you get a result of FALSE then your dates/times are not true Excel dates/times. They are TEXT strings. -- Biff Microsoft Excel MVP "Jackajoo" wrote in message ... I have two columns with date and times. I need a formula to show that the date in the second column was no later than 8am the day after the date/time in the first column. For example: A1 04/05/09 14:35 PM B1 04/06/09 11:01 AM So using my explanation above, this would be "not compliant" with the scenario and I would like an "N" returned. A compliant example would be: A1 04/06/09 14:35 PM B1 04/07/09 07:30 AM Is there a formula for this? Thanks!!! . . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Time - By 8am the next morning
is there any way I can exclude weekends?
How do you mean exclude weekends? Do you mean if the date in cell A1 is a Friday then the date/time in B1 has to before Monday 8:00 AM? -- Biff Microsoft Excel MVP "Jackajoo" wrote in message ... This is great - however - is there any way I can exclude weekends? I just copiy andpasted the formula and it worked!!! "T. Valko" wrote: Try this... Assuming that the date/time in B1 will *always* be later than the date/time in A1. =IF(COUNT(A1:B1)=2,IF(B1INT(A1+1)+TIME(8,0,0),"N" ,""),"") If there will *always* be entries in both cells then you can shorten that to: =IF(B1INT(A1+1)+TIME(8,0,0),"N","") -- Biff Microsoft Excel MVP "Jackajoo" wrote in message ... Hi! I did what you asked and I got a "TRUE" answer. It just depends on where you are viewing the field. If you look at the actual cell, it has "Army Time" (using the 24 hour clock) and if you look at the top (the formula bar), it is in what I call "regular time" (using the 12 hour clock). Let me know if you can help! I appreciate it! "T. Valko" wrote: Are you sure your dates/times are true Excel dates/times? Excel doesn't recognize those as true dates/times when you have the AM/PM following a 24hr time format: 14:35 PM This would be a true Excel date/time: 04/05/09 2:35 PM One way to test is to see if the date/time is a number. In Excel, true dates/times are really just numbers formatted to look like dates/times. Let's assume cell A1 contains 04/05/09 14:35 PM. What result do you get from this formula: =ISNUMBER(A1) If you get a result of FALSE then your dates/times are not true Excel dates/times. They are TEXT strings. -- Biff Microsoft Excel MVP "Jackajoo" wrote in message ... I have two columns with date and times. I need a formula to show that the date in the second column was no later than 8am the day after the date/time in the first column. For example: A1 04/05/09 14:35 PM B1 04/06/09 11:01 AM So using my explanation above, this would be "not compliant" with the scenario and I would like an "N" returned. A compliant example would be: A1 04/06/09 14:35 PM B1 04/07/09 07:30 AM Is there a formula for this? Thanks!!! . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need to display "Good Morning" or "Good Evening" based on NOW() | Excel Worksheet Functions | |||
| Formula doesn't work this morning, but worked for 2 years now | Excel Worksheet Functions | |||
| Saturday morning cut and paste help | Excel Discussion (Misc queries) | |||
| Simple, Monday morning brain function | Excel Worksheet Functions | |||
| Simple, Monday morning brain function | Excel Worksheet Functions | |||
Hybrid Mode
