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

New Contributor

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

 

Laptop#2Laptop#2

3 Replies

@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 Hi! Even if I changed the date format into MM/dd/yyyy in Laptop#2. Same error.error.png

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