| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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
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
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Time formula (difference of predicted and actual time) | Excel Discussion (Misc queries) | |||
| formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
| convert from percentage of time to time using complex formula in . | Excel Worksheet Functions | |||
| Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
| template or formula for start time -finish time -total hours ple | New Users to Excel | |||
Linear Mode
