Forum Discussion

badbaby's avatar
badbaby
Copper Contributor
May 24, 2024

Formulas from a downloaded Excel file are not working

Hello,

I have a downloaded Excel file that uses basic TIMEVALUE, DATEVALUE, INDEX/MATCH functions. However in my Excel and ONLY in my Excel, those are not working properly. The formulas only return #VALUE! errors. I've tested downloading said file from 2 other computers with the same Excel version (2019) but those are working. 

 

Here's a sample:

The format of the dates under Date of Bid are dd/mm/yyyy hh:mm:ss.

 

Thank you.

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor

    badbaby There seem to be two issues here.

    1. The formula appears wrong, you achieve the same result by using the formula =INT(C2)

    2. The formula fails because of your Region settings. The format text in the TEXT function contains characters which do not match the characters for date and time elements of your language.

    For example: I am in The Netherlands, I have to change the string "dd/mm/yyyy" to "dd/m/jjjj" for the formula to work on my system.

Resources