#Value error in formula

[albiman]

Hi,

I have a formula =IF(OR('Accrual Template'!E12="Select Accrual Category",'Accrual Template'!E12=""),"",EOMONTH(DATE('Accrual Template'!$D$4,MONTH(DATEVALUE('Accrual Template'!$D$3&"1")),1),0))
the point is that if in the accrual template the E12 is empty or "Select Accrual Category", it would return "" blank result, if False it would return a date. Odd thing is that in mu excel it works without issue. But one of the users got #VALUE! error. I evaluated the formula and only #N/A error is with the TRUE returning "". In my excel if I just press Enter in the formula bar it shows the correct result. Do not understand what is the problem in the user's excel.
Please help.

 

[James006]

Hi,
Would seem you have to check the exact contents of cell E12 ... hopefully data was not deleted with the space bar ...

 

[jasonb75]

Is the other person using different regional settings?

Looking at your formula I believe that you are using international date format "yy/mm/dd" but if that is not correct then users with different date settings to yours (UK and USA for example) would encounter errors.

What is in D3?

 

[albiman]

thanks for the quick answers

in D3 and D4 is this:

according the evaluation of the formula the error is not coming from the TRUE value "" returning? Btw we used this "" in the past as well.

Where can we check the regional setting?

 

[jasonb75]

Where can we check the regional setting?

Assuming windows, you would check it in Control Panel > Time and Date settings, it is not set in Excel.

If the other person is set up to use a language other than English then that would likely be the problem.

 

[albiman]

I checked with the user, they use the old formula now: =IF(OR('Accrual Template'!E10="Select Accrual Category",'Accrual Template'!E10=""),"",INDEX('check rules'!$X$2:$X$13,MATCH('Accrual Template'!$D$3,'check rules'!$V$2:$V$13,0)))

and it is working for them, they use the same Date and Time setting as well

 

[albiman]

update, they use Windows and german language.
In the Date time settings in Control panel I do not see any language option btw

 

[jasonb75]

I don't remember exactly where it is, there could be a Regional Settings option on the main control panel window before going into date and time. I'm using an android device at the moment so can't check exact options.

Note that it is probably best not to change it as it will affect everything, not just excel.

 

[albiman]

ok I think I found the date and time setup:
this is mine:

and this is user's:

but when they use the old formula, should not be the same problem with the date format?

 

[albiman]

and this is the format of the final return, it is Custom, not Date