• 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

F

Falinaicare

Member
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

  • iferror date.xlsx
    9.3 KB · Views: 6
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
 
Hui said:
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
Click to expand...
Thanks Hui for your comments. Unfortunately, bosco_yip knows my issue and solve it.
 
Falinaicare said:
@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.
Click to expand...
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:
The F9, why the #value, I have retype the same as your formula but kind of strange. The rest of it workable.
 
bosco_yip said:
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
Click to expand...

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

  • iferror date (1).xlsx
    10.4 KB · Views: 7
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
 
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
 
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")
 
pecoflyer said:
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
Click to expand...
pecoflyer
Thanks it works finally by using the "Click Data - Text to columns - Finish".
 
Excel Wizard said:
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")
Click to expand...
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.
 
You must log in or register to reply here.
Back
Top