Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
There are several strings to this question.
1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the elapsed time between C3 and today if B3 is blank - but doesn't. 2) The formulae work OK except they return FALSE when cell N78 (DVLA cell) has anything other than DVLA. Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2) Option 2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2))) 3) Invoices are raised every month for vehicles kept in store. Two things; I need to a) carry-over to a new worksheet, vehicles that remain unclaimed and in store. and b) reset the clock to accumulate the next months' storage charge. Finally. When this has all been piloted for a month or twoI want to migrate it to MS ACCESS so that data can be kept clean. Does anyone know whether the Excel calculations migrate to Access? |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi
1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the elapsed time between C3 and today if B3 is blank - but doesn't What does it return? Are you sure C3 is Null and not a SPACE " ", or a 0 with ToolsOptionsViewZero values unchecked? Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2) should be =IF(AND(AD78="80",N78="Scrap/AV"),AD78="DVLA",$AE$2,"") Option 2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2))) should be =IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD77 =""),$AE$2,""))) a) carry-over to a new worksheet, vehicles that remain unclaimed Take a look at Advanced Filter Debra Dalgleish has some great guidance on her site http://www.contextures.com/xladvfilter01.html#ExtractWs b) reset the clock to accumulate the next months' storage charge. you don't say what the clock is? Presumably a cell on the sheet which holds reference date. -- Regards Roger Govier "Michell Major" wrote in message ... There are several strings to this question. 1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the elapsed time between C3 and today if B3 is blank - but doesn't. 2) The formulae work OK except they return FALSE when cell N78 (DVLA cell) has anything other than DVLA. Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2) Option 2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2))) 3) Invoices are raised every month for vehicles kept in store. Two things; I need to a) carry-over to a new worksheet, vehicles that remain unclaimed and in store. and b) reset the clock to accumulate the next months' storage charge. Finally. When this has all been piloted for a month or twoI want to migrate it to MS ACCESS so that data can be kept clean. Does anyone know whether the Excel calculations migrate to Access? |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Many thanks Roger
"Roger Govier" wrote: Hi 1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the elapsed time between C3 and today if B3 is blank - but doesn't What does it return? Are you sure C3 is Null and not a SPACE " ", or a 0 with ToolsOptionsViewZero values unchecked? Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2) should be =IF(AND(AD78="80",N78="Scrap/AV"),AD78="DVLA",$AE$2,"") Option 2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2))) should be =IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD77 =""),$AE$2,""))) a) carry-over to a new worksheet, vehicles that remain unclaimed Take a look at Advanced Filter Debra Dalgleish has some great guidance on her site http://www.contextures.com/xladvfilter01.html#ExtractWs b) reset the clock to accumulate the next months' storage charge. you don't say what the clock is? Presumably a cell on the sheet which holds reference date. -- Regards Roger Govier "Michell Major" wrote in message ... There are several strings to this question. 1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the elapsed time between C3 and today if B3 is blank - but doesn't. 2) The formulae work OK except they return FALSE when cell N78 (DVLA cell) has anything other than DVLA. Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2) Option 2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2))) 3) Invoices are raised every month for vehicles kept in store. Two things; I need to a) carry-over to a new worksheet, vehicles that remain unclaimed and in store. and b) reset the clock to accumulate the next months' storage charge. Finally. When this has all been piloted for a month or twoI want to migrate it to MS ACCESS so that data can be kept clean. Does anyone know whether the Excel calculations migrate to Access? |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Roger. I made a mess of the original formula so have corrected it. However
it still doesn't work and returns VALUE! A3 is date received, B3 is date actioned,C3 is where the formula is for elapsed time. The simple formula is B3-A3=elapsed time. And yes, to the 3rd string question, the clock start time is A3 when we receive the vehicle. "Roger Govier" wrote: Hi 1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the elapsed time between C3 and today if B3 is blank - but doesn't What does it return? Are you sure C3 is Null and not a SPACE " ", or a 0 with ToolsOptionsViewZero values unchecked? Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2) should be =IF(AND(AD78="80",N78="Scrap/AV"),AD78="DVLA",$AE$2,"") Option 2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2))) should be =IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD77 =""),$AE$2,""))) a) carry-over to a new worksheet, vehicles that remain unclaimed Take a look at Advanced Filter Debra Dalgleish has some great guidance on her site http://www.contextures.com/xladvfilter01.html#ExtractWs b) reset the clock to accumulate the next months' storage charge. you don't say what the clock is? Presumably a cell on the sheet which holds reference date. -- Regards Roger Govier "Michell Major" wrote in message ... There are several strings to this question. 1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the elapsed time between C3 and today if B3 is blank - but doesn't. 2) The formulae work OK except they return FALSE when cell N78 (DVLA cell) has anything other than DVLA. Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2) Option 2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2))) 3) Invoices are raised every month for vehicles kept in store. Two things; I need to a) carry-over to a new worksheet, vehicles that remain unclaimed and in store. and b) reset the clock to accumulate the next months' storage charge. Finally. When this has all been piloted for a month or twoI want to migrate it to MS ACCESS so that data can be kept clean. Does anyone know whether the Excel calculations migrate to Access? |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Dan
My guess is still that cell A3 contains a space. What do you get for =LEN(A3)? -- Regards Roger Govier "Michell Major" wrote in message ... Roger. I made a mess of the original formula so have corrected it. However it still doesn't work and returns VALUE! A3 is date received, B3 is date actioned,C3 is where the formula is for elapsed time. The simple formula is B3-A3=elapsed time. And yes, to the 3rd string question, the clock start time is A3 when we receive the vehicle. "Roger Govier" wrote: Hi 1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the elapsed time between C3 and today if B3 is blank - but doesn't What does it return? Are you sure C3 is Null and not a SPACE " ", or a 0 with ToolsOptionsViewZero values unchecked? Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2) should be =IF(AND(AD78="80",N78="Scrap/AV"),AD78="DVLA",$AE$2,"") Option 2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2))) should be =IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD77 =""),$AE$2,""))) a) carry-over to a new worksheet, vehicles that remain unclaimed Take a look at Advanced Filter Debra Dalgleish has some great guidance on her site http://www.contextures.com/xladvfilter01.html#ExtractWs b) reset the clock to accumulate the next months' storage charge. you don't say what the clock is? Presumably a cell on the sheet which holds reference date. -- Regards Roger Govier "Michell Major" wrote in message ... There are several strings to this question. 1) =IF(C3="",TODAY()-B3),C3-B3. This is meant to calculate the elapsed time between C3 and today if B3 is blank - but doesn't. 2) The formulae work OK except they return FALSE when cell N78 (DVLA cell) has anything other than DVLA. Option 1: =IF(AND(AD78="80",N78="Scrap/AV",""),AD78="DVLA",$AE$2) Option 2:=IF(N77="","",IF(AD77=80,"",IF(AND(N77="DVLA",AD 77=""),$AE$2))) 3) Invoices are raised every month for vehicles kept in store. Two things; I need to a) carry-over to a new worksheet, vehicles that remain unclaimed and in store. and b) reset the clock to accumulate the next months' storage charge. Finally. When this has all been piloted for a month or twoI want to migrate it to MS ACCESS so that data can be kept clean. Does anyone know whether the Excel calculations migrate to Access? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|