将JSON日期转换为MM / DD / YYYY格式？ [英] Convert JSON Date To MM/DD/YYYY Format?

问题描述

我的客户收到一个包含多列的电子表格，一个是日期。只有日期才被格式化为 Date（1292291582263-0700）（似乎是一个JSON日期）。

My client receives a spreadsheet with a number of columns, one being a "date". Only the date turns out to be formatted as Date(1292291582263-0700) (a JSON date it seems).

我需要使用MM / DD / YYYY格式的此JSON日期转换并使用此电子表格代码（VBA）中的其他位置。有没有人知道如何解析并将这个JSON日期格式转换成MM / DD / YYYY格式？

I need to convert and work with this JSON date in MM/DD/YYYY format, elsewhere in this spreadsheet's code (VBA).

我已经阅读了许多关于JavaScript，C＃或ASP.NET等的解决方案，但是我所需要处理的只有Excel 2010和这个项目的VBA代码。有没有办法得到一个可读的格式，我需要？

Does anyone know how to parse and convert this JSON date format into a MM/DD/YYYY format? I have read lots of solutions on SO that are in Javascript, C#, or ASP.NET, etc but all I have to work with is Excel 2010 and VBA code for this project. Is there way to arrive at a readable format as I need?

推荐答案

毫秒时间与+/-偏移？

Millisecond Epoch time with a +/- offset?

Const test = "1292291582263-0700"

Dim dt As String: dt = Left$(test, 13)
Dim off As String: off = Mid$(test, 14)

Dim d As Date: d = DateAdd("s", CCur(dt) / 1000, "01/01/1970")
Debug.Print d
<<< 14/12/2010 01:53:02 

d = DateAdd("h", Left$(off, 3), d)
d = DateAdd("n", Right$(off, 2), d)
Debug.Print d
<<< 13/12/2010 18:53:02 

这篇关于将JSON日期转换为MM / DD / YYYY格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！