• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Iferror on the date

Dear expert,

Good day.
Capture.PNG

I am using this function =IFERROR(D10+E10,"Upon Request") to show the next loan date.

Question here is what if I want to display the "Upon Request" on the XXX Company rather than the next loan date is 15/1/2021?

Kindly assists with thanks.
 

Attachments

Hui

Excel Ninja
Staff member
Do you mean G10 as:
="-------> Upon Request on the "&C10

or do you want that to display when E10 is 0 ?
If that is the case
G10: =IF(E10=0,"-------> Upon Request on the "&C10,"")

or G9: =IF(F9=D9,"-------> Upon Request on the "&C9,"")
copy G9 down
 
Do you mean G10 as:
="-------> Upon Request on the "&C10

or do you want that to display when E10 is 0 ?
If that is the case
G10: =IF(E10=0,"-------> Upon Request on the "&C10,"")

or G9: =IF(F9=D9,"-------> Upon Request on the "&C9,"")
copy G9 down
Thanks Hui for your comments. Unfortunately, bosco_yip knows my issue and solve it.
 

bosco_yip

Excel Ninja
@bosco_yip I have tried your if function successfully but I have a small issue as image below:

View attachment 74223

The F9, why the #value, I have retype the same as your formula but kind of strange. The rest of it workable.

Hope to hear from you.

Thanks.
74230

From the above screenshot in your post #5

It is a region date setting problem.

1] D9: 20/12/2020 ...> it is a UK date format setting (that is the reason F9 formula return error)

2] D10: 1/15/2021 ...> it is a USA date format setting

3] F11: 2/1/2020 ...> it is a USA date format setting (judge from the formula result)

So that,

Change D9 to USA data format >> 12/20/2020 will solve the problem.

Regards
 
Last edited:

bernardand

New Member
The F9, why the #value, I have retype the same as your formula but kind of strange. The rest of it workable.
 
View attachment 74230

From the above screenshot in your post #5

It is a region date setting problem.

1] D9: 20/12/2020 ...> it is a UK date format setting (that is the reason F9 formula return error)

2] D10: 1/15/2021 ...> it is a USA date format setting

3] F11: 2/1/2020 ...> it is a USA date format setting (judge from the formula result)

So that,

Change D9 to USA data format >> 12/20/2020 will solve the problem.

Regards
bosco_yip

I have tried to change to USA date format but it still the same error #value. Very strange.
I have captured the image for you and uploaded the excel file for your reference. (If you can see the strange row)

Thus, I have also tried to copied back from the F11 and it still won't give me the date value.

The only one thing I can do is to delete the row - F9 and recreate it, then the #value will not there.
Sound good?

74270
 

Attachments

vletm

Excel Ninja
Falinaicare
D9 is day/month/year
D10 is month/day/year
D11 is ... can be both ( day/month/year or month/day/year)
D12 is ... can be both ( day/month/year or month/day/year)
>> write all those dates day/month/year or month/day/year
 

pecoflyer

Active Member
First remove the manual alignment in col D so you can see the difference between text and numbers
You now see that XL thinks D9 is text ( left-aligned)
Select D9-D12 - Click Data - Text to columns - Finish.
Does this work ?
FWIW cell formatting has nothing to do with underlying values. It is only the way XL shows values to the outside world.
In your case XL sees D9 as text
 

Excel Wizard

Active Member
Maybe try

=IF(E9,IF(N(D9),D9,SUBSTITUTE(MID(D9,FIND("/",D9)+1,9),"/","/"&LEFT(D9,FIND("/",D9))))+E9,"Upon Request")

or
=IF(E9,IF(N(D9),D9,RIGHT(D9,4)&MID(LEFT(D9,FIND("/",D9,4)),FIND("/",D9),4)&LEFT(D9,FIND("/",D9)-1))+E9,"Upon Request")
 
First remove the manual alignment in col D so you can see the difference between text and numbers
You now see that XL thinks D9 is text ( left-aligned)
Select D9-D12 - Click Data - Text to columns - Finish.
Does this work ?
FWIW cell formatting has nothing to do with underlying values. It is only the way XL shows values to the outside world.
In your case XL sees D9 as text
pecoflyer
Thanks it works finally by using the "Click Data - Text to columns - Finish".
 
Maybe try

=IF(E9,IF(N(D9),D9,SUBSTITUTE(MID(D9,FIND("/",D9)+1,9),"/","/"&LEFT(D9,FIND("/",D9))))+E9,"Upon Request")

or
=IF(E9,IF(N(D9),D9,RIGHT(D9,4)&MID(LEFT(D9,FIND("/",D9,4)),FIND("/",D9),4)&LEFT(D9,FIND("/",D9)-1))+E9,"Upon Request")
Excel Wizard
Thank you for your long formulas. I have tried both but at the end, I have to "Click Data - Text to columns - Finish" then I can see it overall without delete the row and recreate it.
 
Top