Need help with Micro in excel

[curo]

Hi, I'm trying to create a cell that will display the last saved date of the worksheet automatically.

I did a google search and found this code:

Function LastSaved() As Date
Application.Volatile
LastSaved = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

But the problem now is when I formatted the cell to display date, the date is incorrect. It show year 2017 and date is next day. So its like showing the future. Anyone know how can I correct this problem so show tat it is today date?

Thanks

 

[ctingyee]

Can you check your computer's time (just mouse over the clock)? Maybe it is showing the date you saw or you forgot to change the time back while using function.

I used the function in module shows date is okay.

 

[curo]

Can you check your computer's time (just mouse over the clock)? Maybe it is showing the date you saw or you forgot to change the time back while using function.

I used the function in module shows date is okay.

I checked. My computer time and year is correct. Time also correct. That is why I am stuck..

 

[ctingyee]

I checked. My computer time and year is correct. Time also correct. That is why I am stuck..

Okay... How about that excel file. Can you check it at explorer? If the file last saved on 'future' date, I think is correct.

Haven't you edited the file, saved it, closed and reopen the file to check the result?

 

[curo]

Okay... How about that excel file. Can you check it at explorer? If the file last saved on 'future' date, I think is correct.

Haven't you edited the file, saved it, closed and reopen the file to check the result?

i even tried the formula =date(). It show the correct date and year. I redo the micro but same problem. (edited, save, close and reopen)

 

[ctingyee]

Interesting. I used Excel 2010, new macro-enable excel file. Looks fine to me.

If I saved today, I get the value 41508.49111 in number. Before you set to date format, what's the value?

i even tried the formula =date(). It show the correct date and year. I redo the micro but same problem. (edited, save, close and reopen)

 

[curo]

Interesting. I used Excel 2010, new macro-enable excel file. Looks fine to me.

If I saved today, I get the value 41508.49111 in number. Before you set to date format, what's the value?

I got 41508.47159.

So different from yours

 

[ctingyee]

That value is correct, 41508 means today date (2013/08/22) and the floating value is the time.

If you format date on cell that value 1.99999 should be 1900/01/01 23:59:59

I don't know why your 41508 refers to year 2017.

...

You use office mac version?

 

[ctingyee]

Updated

I think your workbook use 1904 system.

To disable it, go to File -> Options -> Advanced -> When calculating this workbook (tab), uncheck "Use 1904 date system" (Alt-y to quick toggle) then Okay.

This should follow fix your problem

Refer: Differences between the 1900 and the 1904 date system in Excel

 

[curo]

Updated

I think your workbook use 1904 system.

To disable it, go to File -> Options -> Advanced -> When calculating this workbook (tab), uncheck "Use 1904 date system" (Alt-y to quick toggle) then Okay.

This should follow fix your problem

Refer: Differences between the 1900 and the 1904 date system in Excel

Yes.. I got it.. Thanks.. This is the solution.