Function GetValue() returns data not in date format

Docs
1

I need to get date from a cell but function GetValue() keeps returning data in standart format.
example: 06.12.2023 but it returns 45266
Basicaly I need to get it and put in a Date variable.
What can I do in this situation? Should I use other function or is it api issue?

3

2

Hello @AlexAndr

You have to additionally set number format for the cell with method SetNumberFormat.
Here is a small example:

var oSheet = Api.GetActiveSheet();

var dataValue = oSheet.GetRange("A1").GetValue();

oSheet.GetRange("C3").SetValue(dataValue);
oSheet.GetRange("C3").SetNumberFormat("m/d/yyyy");
oSheet.GetRange("C3").AutoFit(true, false);

There is a date in cell A1 that is copied to variable dataValue , then pasted to the cell C3 and format is applied. Also I’ve added AutoFit method to avoid insufficient amount of space for the date that would cause date to be displayed like #####. The result is:
image

1 Like
3

Basically I was confused with what data GetValue() function keeps returning to me . I took me a while to understand that it returns the number of days that passed since 01/01/1900 :smiley: . It would be nice if it returned data in format that is represented in the cell actually , beacause it can be veeeery confusing especially for newbies like myself. But thanks anyways! P.S. Also I noticed that javascript counts days from 01/01/1970 not from 01/01/1900 , so it must be taken into account when someone write code. It would be nice if you mention that in the manual somewhere :slight_smile:

4

Indeed, JS counts days from 1970 but VBA does from 1900, please correct me if I’m wrong.

5

Pardon me , but dont get why you mentioned VBA .

6

VBA is used in various text processors like MS Office, Libre Office, etc. It is a starting point, so to provide even experience with macros, in our editors date count starts from 01/01/1900 just like in VBA.

7

Hello
How get and insert date with hours, minutes and seconds
Data in cell ‘03.02.2024 9:30:58’ data in new cell ‘45325.39650462963’ but i set format ‘Api.GetActiveSheet().GetRange(‘A4’).SetNumberFormat(‘dd/mm/yyyy h/mm/ss’);’

(function()
{
    var data = Api.GetActiveSheet().GetRange('B4').GetValue();
    Api.GetActiveSheet().GetRange('A4').SetValue(data);
    Api.GetActiveSheet().GetRange('A4').SetNumberFormat('dd/mm/yyyy h/mm/ss');
    Api.GetActiveSheet().GetRange('A4').AutoFit(true, false);
})();
8

Hello @siniGAL

I was not able to replicate the issue with your macro sample and provided data.
The scenario I followed is:

  1. Inserted a value 03.02.2024 9:30:58 into B4 cell;
  2. Executed provided macros.

The value in A4 cell is displayed as 03.02.2024 9:30:58 after executing the macro as expected. Did I misunderstand anything?

By the way, what version of ONLYOFFICE Docs (Document Server) are you using?