question

MartinAshcroft-4812 avatar image
0 Votes"
MartinAshcroft-4812 asked 35247258 edited

Graph API - Updating Excel table. Day and month are being switched around on some dates.

Hi,

I am updating a table in an Excel workbook and I am having a strange issue with the date ranges.

This following request body:

{
"values": [
[
"05/12/2021 14:48:0",
"123457",
"Smith",
"5/1/2021 14:48:0",
"Denied",
"",
""
]
]
}

Translates to the following within the sheet

12/05/2021 14:48 123457 Smith 01/05/2021 14:48 Denied


Whereas the following request body:

{
"values": [
[
"15/12/2021 14:48:0",
"123457",
"Smith",
"15/1/2021 14:48:0",
"Denied",
"",
""
]
]
}

Becomes:

15/12/2021 14:48:0 123457 Smith 15/1/2021 14:48:0 Denied


The first request has swapped the date and month around whereas the second request is displaying as I would expect.

Does anyone have any suggestions as to why this may be happening?

office-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MartinAshcroft-4812 avatar image
0 Votes"
MartinAshcroft-4812 answered

For my solution, it was fine to just format the sheet as a text field but it seems I would have to use the serial number convention if I wanted to use the field as a proper date time field.

https://www.exceltactics.com/definitive-guide-using-dates-times-excel/#How-Excel-Stores-Times

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.