|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 12th, 2009, 04:39 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF formula for time
I have two columns
Time taken to complete work Completed within 24hrs 23:00 Yes 25:00 No I want to know if anything has gone over a 24hr time limit before I had it calculate =IF(C324,"NO","YES") so if C3 was 25:00 it would give me a NO or 23:00 a YES right now it gives me all YES even if it is over 24 Do I have to change the format of =IF cell or input a new formula Thanks for your time 
|
|
#2
June 12th, 2009, 04:43 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula for time
=IF(C3TIME(24,0,0),"NO","YES")
If this post helps click Yes --------------- Jacob Skaria "Joshua" wrote: I have two columns Time taken to complete work Completed within 24hrs 23:00 Yes 25:00 No I want to know if anything has gone over a 24hr time limit before I had it calculate =IF(C324,"NO","YES") so if C3 was 25:00 it would give me a NO or 23:00 a YES right now it gives me all YES even if it is over 24 Do I have to change the format of =IF cell or input a new formula Thanks for your time
|
|
#3
June 12th, 2009, 05:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula for time
It gives me all NO's
This is my sheet the cell it is calculating is a formula (B2-A2) and in a custom [h]:mm format start date complete date hrs to complete complete within 24hr 7/1/2009 8:30 AM 7/1/2009 9:30 AM 1:00 (=B2-A2) NO 7/1/2009 8:30 AM 7/2/2009 10:30 AM 26:00 NO "Jacob Skaria" wrote: =IF(C3TIME(24,0,0),"NO","YES") If this post helps click Yes --------------- Jacob Skaria "Joshua" wrote: I have two columns Time taken to complete work Completed within 24hrs 23:00 Yes 25:00 No I want to know if anything has gone over a 24hr time limit before I had it calculate =IF(C324,"NO","YES") so if C3 was 25:00 it would give me a NO or 23:00 a YES right now it gives me all YES even if it is over 24 Do I have to change the format of =IF cell or input a new formula Thanks for your time
|
|
#4
June 12th, 2009, 05:06 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula for time
=IF(C3TIME(24,0,0),"NO","YES")
Look in Excel help on the TIME function and note what it says about the HOUR argument. Try it like this: =IF(C31,"NO","YES") -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... =IF(C3TIME(24,0,0),"NO","YES") If this post helps click Yes --------------- Jacob Skaria "Joshua" wrote: I have two columns Time taken to complete work Completed within 24hrs 23:00 Yes 25:00 No I want to know if anything has gone over a 24hr time limit before I had it calculate =IF(C324,"NO","YES") so if C3 was 25:00 it would give me a NO or 23:00 a YES right now it gives me all YES even if it is over 24 Do I have to change the format of =IF cell or input a new formula Thanks for your time
|
|
#5
June 12th, 2009, 05:09 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula for time
Oops....Its my mistake Joshua
=IF(TEXT(C3,"[h]")+024,"No","Yes") If this post helps click Yes --------------- Jacob Skaria "Joshua" wrote: It gives me all NO's This is my sheet the cell it is calculating is a formula (B2-A2) and in a custom [h]:mm format start date complete date hrs to complete complete within 24hr 7/1/2009 8:30 AM 7/1/2009 9:30 AM 1:00 (=B2-A2) NO 7/1/2009 8:30 AM 7/2/2009 10:30 AM 26:00 NO "Jacob Skaria" wrote: =IF(C3TIME(24,0,0),"NO","YES") If this post helps click Yes --------------- Jacob Skaria "Joshua" wrote: I have two columns Time taken to complete work Completed within 24hrs 23:00 Yes 25:00 No I want to know if anything has gone over a 24hr time limit before I had it calculate =IF(C324,"NO","YES") so if C3 was 25:00 it would give me a NO or 23:00 a YES right now it gives me all YES even if it is over 24 Do I have to change the format of =IF cell or input a new formula Thanks for your time
|
|
#6
June 12th, 2009, 05:11 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula for time
Hi,
Change your formula to read =IF(C31,"NO","YES") To understand why chage the format of C3 to number, many decimals. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Joshua" wrote: It gives me all NO's This is my sheet the cell it is calculating is a formula (B2-A2) and in a custom [h]:mm format start date complete date hrs to complete complete within 24hr 7/1/2009 8:30 AM 7/1/2009 9:30 AM 1:00 (=B2-A2) NO 7/1/2009 8:30 AM 7/2/2009 10:30 AM 26:00 NO "Jacob Skaria" wrote: =IF(C3TIME(24,0,0),"NO","YES") If this post helps click Yes --------------- Jacob Skaria "Joshua" wrote: I have two columns Time taken to complete work Completed within 24hrs 23:00 Yes 25:00 No I want to know if anything has gone over a 24hr time limit before I had it calculate =IF(C324,"NO","YES") so if C3 was 25:00 it would give me a NO or 23:00 a YES right now it gives me all YES even if it is over 24 Do I have to change the format of =IF cell or input a new formula Thanks for your time
|
|
#7
June 12th, 2009, 05:13 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula for time
Excel stores time as fraction of a day. So 8:00 is STORED as 8/24 or 0.3333.
It is DISPLAYED as 8:00 Enter 8:00 in a cell and then format it to display as Number and you will see 0.33333.... So to test if C3 is greater than 24 HOURS you can use =IF(C3*2424,"NO","YES") best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Joshua" wrote in message ... I have two columns Time taken to complete work Completed within 24hrs 23:00 Yes 25:00 No I want to know if anything has gone over a 24hr time limit before I had it calculate =IF(C324,"NO","YES") so if C3 was 25:00 it would give me a NO or 23:00 a YES right now it gives me all YES even if it is over 24 Do I have to change the format of =IF cell or input a new formula Thanks for your time
|
|
#8
June 12th, 2009, 05:17 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula for time
Thanks Biff or in other words that means its time for me to sleep..
If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: =IF(C3TIME(24,0,0),"NO","YES") Look in Excel help on the TIME function and note what it says about the HOUR argument. Try it like this: =IF(C31,"NO","YES") -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... =IF(C3TIME(24,0,0),"NO","YES") If this post helps click Yes --------------- Jacob Skaria "Joshua" wrote: I have two columns Time taken to complete work Completed within 24hrs 23:00 Yes 25:00 No I want to know if anything has gone over a 24hr time limit before I had it calculate =IF(C324,"NO","YES") so if C3 was 25:00 it would give me a NO or 23:00 a YES right now it gives me all YES even if it is over 24 Do I have to change the format of =IF cell or input a new formula Thanks for your time
|
|
#9
June 12th, 2009, 05:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF formula for time
Either that or you need to go a have a few cold ones!
-- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Thanks Biff or in other words that means its time for me to sleep.. If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: =IF(C3TIME(24,0,0),"NO","YES") Look in Excel help on the TIME function and note what it says about the HOUR argument. Try it like this: =IF(C31,"NO","YES") -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... =IF(C3TIME(24,0,0),"NO","YES") If this post helps click Yes --------------- Jacob Skaria "Joshua" wrote: I have two columns Time taken to complete work Completed within 24hrs 23:00 Yes 25:00 No I want to know if anything has gone over a 24hr time limit before I had it calculate =IF(C324,"NO","YES") so if C3 was 25:00 it would give me a NO or 23:00 a YES right now it gives me all YES even if it is over 24 Do I have to change the format of =IF cell or input a new formula Thanks for your time
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
