|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com