| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time for IFERROR?
I am calculating the time between two dates. In the formula, I account for
the dates being reversed by a conditional. Sometimes the data points are "N/A" if there shouldn't be a date. The formula below returns a "#VALUE!" error. Is there a way using IFERROR to return a blank? =IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)0,('Raw Data'!F3-'Raw Data'!E3)/$J$1,"") Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time for IFERROR?
=IFERROR(IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)0,('Raw Data'!F3-'Raw
Data'!E3)/$J$1,""),"") Tyro "PAL" wrote in message ... I am calculating the time between two dates. In the formula, I account for the dates being reversed by a conditional. Sometimes the data points are "N/A" if there shouldn't be a date. The formula below returns a "#VALUE!" error. Is there a way using IFERROR to return a blank? =IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)0,('Raw Data'!F3-'Raw Data'!E3)/$J$1,"") Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time for IFERROR?
Tyro,
It didn't work. It came back with "#Name?" "Tyro" wrote: =IFERROR(IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)0,('Raw Data'!F3-'Raw Data'!E3)/$J$1,""),"") Tyro "PAL" wrote in message ... I am calculating the time between two dates. In the formula, I account for the dates being reversed by a conditional. Sometimes the data points are "N/A" if there shouldn't be a date. The formula below returns a "#VALUE!" error. Is there a way using IFERROR to return a blank? =IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)0,('Raw Data'!F3-'Raw Data'!E3)/$J$1,"") Thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time for IFERROR?
Do you mean a missing date
=IF(OR('Raw Data'!E3="",'Raw Data'!F3=""),"",N(ABS('Raw Data'!F3-'Raw Data'!E3))/$J$1) or a date of #NA() =IF(OR(ISNA('Raw Data'!E3),ISNA('Raw Data'!F3)),"",N(ABS('Raw Data'!F3-'Raw Data'!E3))/$J$1) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "PAL" wrote in message ... I am calculating the time between two dates. In the formula, I account for the dates being reversed by a conditional. Sometimes the data points are "N/A" if there shouldn't be a date. The formula below returns a "#VALUE!" error. Is there a way using IFERROR to return a blank? =IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)0,('Raw Data'!F3-'Raw Data'!E3)/$J$1,"") Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time for IFERROR?
Which version of Excel do you have? Tyro had presumably assumed from your
reference to IFERROR that you were using Excel 2007. If not, the nearest equivalent you'll get is ISERROR, which needs the longer construct of =IF(ISERROR(your_formula),"",your_formula). -- David Biddulph "PAL" wrote in message ... Tyro, It didn't work. It came back with "#Name?" "Tyro" wrote: =IFERROR(IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)0,('Raw Data'!F3-'Raw Data'!E3)/$J$1,""),"") Tyro "PAL" wrote in message ... I am calculating the time between two dates. In the formula, I account for the dates being reversed by a conditional. Sometimes the data points are "N/A" if there shouldn't be a date. The formula below returns a "#VALUE!" error. Is there a way using IFERROR to return a blank? =IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)0,('Raw Data'!F3-'Raw Data'!E3)/$J$1,"") Thanks. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time for IFERROR?
Maybe this:
=IF(COUNT('Raw Data'!E3:F3,$J$1)<3,"",('Raw Data'!F3-'Raw Data'!E3)/$J$1) -- Biff Microsoft Excel MVP "PAL" wrote in message ... I am calculating the time between two dates. In the formula, I account for the dates being reversed by a conditional. Sometimes the data points are "N/A" if there shouldn't be a date. The formula below returns a "#VALUE!" error. Is there a way using IFERROR to return a blank? =IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)0,('Raw Data'!F3-'Raw Data'!E3)/$J$1,"") Thanks. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time for IFERROR?
Hi Bob,
I did mean "N/A", but not the excel error version(#N/A). Users will not understand the "#" symbol. In reality instead of a date, it should be considered text and I assume therein lies the problem. It does work if I include the "#". However, those cells with real dates get the "#REF"error. "Bob Phillips" wrote: Do you mean a missing date =IF(OR('Raw Data'!E3="",'Raw Data'!F3=""),"",N(ABS('Raw Data'!F3-'Raw Data'!E3))/$J$1) or a date of #NA() =IF(OR(ISNA('Raw Data'!E3),ISNA('Raw Data'!F3)),"",N(ABS('Raw Data'!F3-'Raw Data'!E3))/$J$1) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "PAL" wrote in message ... I am calculating the time between two dates. In the formula, I account for the dates being reversed by a conditional. Sometimes the data points are "N/A" if there shouldn't be a date. The formula below returns a "#VALUE!" error. Is there a way using IFERROR to return a blank? =IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)0,('Raw Data'!F3-'Raw Data'!E3)/$J$1,"") Thanks. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Time for IFERROR?
=IF(COUNTIF('Raw Data'!E3:F3,"N/A")0,"",('Raw Data'!F3-'Raw Data'!E3)/$J$1)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "PAL" wrote in message ... Hi Bob, I did mean "N/A", but not the excel error version(#N/A). Users will not understand the "#" symbol. In reality instead of a date, it should be considered text and I assume therein lies the problem. It does work if I include the "#". However, those cells with real dates get the "#REF"error. "Bob Phillips" wrote: Do you mean a missing date =IF(OR('Raw Data'!E3="",'Raw Data'!F3=""),"",N(ABS('Raw Data'!F3-'Raw Data'!E3))/$J$1) or a date of #NA() =IF(OR(ISNA('Raw Data'!E3),ISNA('Raw Data'!F3)),"",N(ABS('Raw Data'!F3-'Raw Data'!E3))/$J$1) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "PAL" wrote in message ... I am calculating the time between two dates. In the formula, I account for the dates being reversed by a conditional. Sometimes the data points are "N/A" if there shouldn't be a date. The formula below returns a "#VALUE!" error. Is there a way using IFERROR to return a blank? =IF((('Raw Data'!F3-'Raw Data'!E3)/$J$1)0,('Raw Data'!F3-'Raw Data'!E3)/$J$1,"") Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Subtracting Dates to get total time work time excluding weekends | Excel Discussion (Misc queries) | |||
| Converting date/time serial values to cumulative time totals... | Excel Discussion (Misc queries) | |||
| verify use of TIME Function, Find Quantity Level compare to time-d | Excel Discussion (Misc queries) | |||
| template or formula for start time -finish time -total hours ple | New Users to Excel | |||
| Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Linear Mode
