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

 

[jasonb75]

Their formula uses a sheet called 'check rules' which I'm guessing contains the translations for the months from English to German.

The DATEVALUE function in your formula will only recognise the months in the language that the system is set up for. Theirs will error in English, yours would error if the month in D3 was entered in German.

 

[albiman]

in the "check rules" tab there is hard written dates in Date format. probably in their excel it shows german setup.

can you recommend a formula instead of DATEVALUE, which would work in every language setup?

 

[James006]

Hi,
Just test the difference between =TEXT(TODAY(),"[$-409]dddd dd mmmm yyyy")

and the exact same formula ... replacing 9 by 7 ...

 

[jasonb75]

Yes, they would show in the system language.

MATCH('Accrual Template'!$D$3,TEXT(SEQUENCE(12,,,29),"[$-409]mmmm"))

Comes to mind as an alternative to the MONTH(DATEVALUE part of the original formula but I'm unable to test it at the moment.

 

[albiman]

hi,
I tried both formulas but did not really work. The point is I would like to get the last day of the month in format YYYYMMDD from below format:

 

[jasonb75]

What exactly is in those cells? Some of your posts indirectly imply that they contain what we see in the screen capture whilst others suggest that the month might be a formated date.

Correct handling within the formula will depend on exactly which you are using.

If you're not sure, it might be easier to upload a sanitised copy to a file sharing location (something like dropbox) and post a link to it so we can check.

 

[albiman]

Thanks all for your help, I think I found a simple solution, I use vlookup from month name to month nbr. So this should not cause any error in any region.

=IF(OR('Accrual Template'!E10="Select Accrual Category",'Accrual Template'!E10=""),"",EOMONTH(DATE('Accrual Template'!$D$4,VLOOKUP('Accrual Template'!$D$3,'check rules'!$L$2:$M$13,2,0),1),0))

And I tested the #Value error root in my original formula as well. The problem was that the frop down list in D3 uses English Month names and other language and region setups use their own. German setup could not define the English month names.
Thanks again for your help.

 

[jasonb75]

I went back and had another look at my earlier theoretical suggestion that I was unable to test at the time. This (corrected version) of my suggestion incorporated into your original formula should work correctly for any language settings without the need for a the separate lookup table.

=IF(OR('Accrual Template'!E12="Select Accrual Category",'Accrual Template'!E12=""),"",DATE('Accrual Template'!$D$4,MATCH('Accrual Template'!$D$3,TEXT(SEQUENCE(12,,29,29),"[$-409]mmmm"),0)+1,0))