Forum Discussion

jajavier's avatar
jajavier
Copper Contributor
Jul 26, 2022

Values not showing on a certain laptop but it's working on other laptop

Hi, I already asked for your Microsoft Tier 2 Support regarding this issue but they adviced me to go here on your community to checked by your Excel Specialist since they can't fixed it.

As you can see, these are the same excel file but it was opened on different laptops. The only difference is that on Laptop#2 it doesn't shows the values but on Laptop#1 it's working.

Here are the formulas used: 
=DATEDIF(N2,R2,"d")
=+Q2/365*S2

 

 

Laptop#1

 

Laptop#2

excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 26, 2022

    jajavier Obviously, Laptop 2 is set to work with dates like dd/mm/yyyy whereas the dates in the file are meant to be read as mm/dd/yyyy. Therefore, all calculations where the second part of any date is greater than 12 will produce a value error. Look at the result in row 4. It takes 6 March 2022 as the start date (not June 3) and 1 Feb 2023 as the end date (not Jan 2).

     

    By the way, you don't really need DATEDIF for this. =R2-N2 will give the same result.

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Jul 26, 2022

        jajavier Did you change the regional setting in Windows on laptop 2? You can't resolve this by just changing the data format in Excel.

        And then you may even need to transform the dates in columns N and R to real dates as most of them are texts. you could try with text to columns and in step 3 indicate the date format that is used in the column, being  MDY.

         

        If you don't succeed, you can perhaps share the file.

         

Resources