# Iferror on the date

#### Falinaicare

##### Member
Dear expert,

Good day.

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

• 9.3 KB Views: 6

#### 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

#### bosco_yip

##### Excel Ninja
Maybe,

Change your formula in F9 -->

From this :

=IFERROR(D9+E9,"Upon Request")

Into this :

=IF(E9,D9+E9,"Upon Request")

#### Falinaicare

##### 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
Thanks Hui for your comments. Unfortunately, bosco_yip knows my issue and solve it.

#### Falinaicare

##### Member
Maybe,

Change your formula in F9 -->

From this :

=IFERROR(D9+E9,"Upon Request")

Into this :

=IF(E9,D9+E9,"Upon Request")

View attachment 74219
@bosco_yip I have tried your if function successfully but I have a small issue as image below:

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.

#### 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.

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.

#### vletm

##### Excel Ninja
bernardand
As written above ... Excel won't figure if use different date formats (dd/mm/yyyy and mm/dd/yyyy).

#### Falinaicare

##### Member
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?

#### Attachments

• 10.4 KB Views: 7

#### 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")

#### Falinaicare

##### 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
pecoflyer
Thanks it works finally by using the "Click Data - Text to columns - Finish".

#### Falinaicare

##### 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")
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.

#### Falinaicare

##### Member
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
vletm
Thanks. It has been solved by your excel members. They are super good.

#### pecoflyer

##### Active Member
And BTW, be VERY careful with IFERROR, it hides ALL errors which might not be what you want