| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If and a date
I have a formula that gives me the first day of the week that a
transaction took place. =K2-WEEKDAY(K2)+2 This works fine so long as there is a date in "K2" but if it is blank then i get a #Value. error I have tried using various "if" statements to say if K2 is blank then the destination cell should also be blank. But all of my "if" statements keep giving errors. (K2 is formatted as dd-mmm-yyyy if that makes a difference.) What is the best way to write this please. Bob |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If and a date
Try the following: =IF(ISBLANK(K2),"",K2-WEEKDAY(K2)+2) Or this, which covers just about everything: IF(ISERROR(K2-WEEKDAY(K2)+2),"",K2-WEEKDAY(K2)+2) -- Kevin Backmann "Box666" wrote: I have a formula that gives me the first day of the week that a transaction took place. =K2-WEEKDAY(K2)+2 This works fine so long as there is a date in "K2" but if it is blank then i get a #Value. error I have tried using various "if" statements to say if K2 is blank then the destination cell should also be blank. But all of my "if" statements keep giving errors. (K2 is formatted as dd-mmm-yyyy if that makes a difference.) What is the best way to write this please. Bob |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If and a date
On 24 Jan, 18:12, Kevin B wrote:
Try the following: =IF(ISBLANK(K2),"",K2-WEEKDAY(K2)+2) Or this, which covers just about everything: IF(ISERROR(K2-WEEKDAY(K2)+2),"",K2-WEEKDAY(K2)+2) -- Kevin Backmann "Box666" wrote: I have a formula that gives me the first day of the week that a transaction took place. =K2-WEEKDAY(K2)+2 This works fine so long as there is adatein "K2" butifit is blank then i get a #Value. error I have tried using various "if" statements to sayifK2 is blank then the destination cell should also be blank. But all of my "if" statements keep giving errors. (K2 is formatted as dd-mmm-yyyyifthat makes a difference.) What is the best way to write this please. Bob- Hide quoted text - - Show quoted text - Thanks, option 2 sorted it. Bob |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
If and a date
If K2 was truly blank, your formula would return -5
You have something in K2 in order to produce the #VALUE! error. A space perhaps or "" created from a formula in K2? Gord Dibben MS Excel MVP On Thu, 24 Jan 2008 10:06:44 -0800 (PST), Box666 wrote: I have a formula that gives me the first day of the week that a transaction took place. =K2-WEEKDAY(K2)+2 This works fine so long as there is a date in "K2" but if it is blank then i get a #Value. error I have tried using various "if" statements to say if K2 is blank then the destination cell should also be blank. But all of my "if" statements keep giving errors. (K2 is formatted as dd-mmm-yyyy if that makes a difference.) What is the best way to write this please. Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dates - Need to display date one month prior to user-entered date | Excel Worksheet Functions | |||
| how to get the random date between the start date and the end date? | Excel Worksheet Functions | |||
| Report Date - Date Recv = Days Late, but how to rid completed date | Excel Worksheet Functions | |||
| Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
| Date updates from worksheet to chart & changes date to a date series! Help!! | Charts and Charting in Excel | |||
Linear Mode
