Excel VBA - 日期格式自动更改 [英] Excel VBA - the date format changes automatically

问题描述

我正在尝试输入日期值，将一个月添加到表格中的日期（Sheet1）。单元格（17,3）.Value是01/10/2011，但格式为Oct-11。然后返回表格（Sheet1）。单元格（17，4）.Value = LDate，显示Nov-11

  sDate = Sheets（Sheet1）。Cells（17，3）.Value  - >这显示01/10/2011当我希望通过sDate 
 LDate = DateAdd（m，1，sDate） - >这显示01/11/2011当我超越LDate 
  

然后我想输入该值01 / 11/2011进入以下单元格

 表格（Sheet1）。单元格（17，4）.Value = LDate 
 Selection.NumberFormat =mmm-yy
  

但在单元格中显示11/01 / 2011（Jan-11），为什么这么做，我该如何解决这个问题？

解决方案

问题

  Selection.NumberFormat =mmm-yy
  / pre> 
 
 
您尚未选择任何内容，因此此格式将放置在离开游标的任何位置。
 
 
 
 主要问题 
 
 
 
如果将数据传输到一个文件，您可以将Excel错误解释为美国（中端）格式的日期工作表。 1/11/2011将是11 Jan 11给美国人，所以它是Excel。 20/11/2011不是有效的美国日期，所以Excel是20 Nov 11。
 
 
 
我可以通过将sDate和LDate声明为字符串。  DateAdd 正确地使用字符串，因此LDate是正确的，但是当放在单元格中时会被误解。
 
 
 
我可以修复您的问题是通过将sDate和LDate声明为日期：
  Dim sdate As Date 
 Dim Ldate As Date 
  
 
I'm trying to enter the Date value by adding a month to the date in Sheets("Sheet1").Cells(17, 3).Value which is 01/10/2011 but format as Oct-11. Then return in Sheets("Sheet1").Cells(17, 4).Value = LDate, to show Nov-11
sDate = Sheets("Sheet1").Cells(17, 3).Value   --> this shows 01/10/2011 when I hove over sDate
LDate = DateAdd("m", 1, sDate)                --> this shows 01/11/2011 when I hove over LDate
I then want to enter that value 01/11/2011 in to the following cell
Sheets("Sheet1").Cells(17, 4).Value = LDate
Selection.NumberFormat = "mmm-yy"
But in the cell it shows  11/01/2011 (Jan-11), why is it doing this and how can I fix this issue?
解决方案
Minor issue
Selection.NumberFormat = "mmm-yy"
You have not selected anything so this format is placed wherever you left the cursor.

Major issue

You have hit the Excel bug that it will interpret dates as being in American (middle endian) format if it can when transferring data to a worksheet.  "1/11/2011" would be "11 Jan 11" to an American so it is to Excel.  "20/11/2011" is not a valid American date so to Excel it is "20 Nov 11".

I can duplicate your problem by declaring sDate and LDate as strings.  DateAdd works correctly with strings so LDate is correct but when placed in a cell it is misinterpreted.

I can fix your problem by declaring sDate and LDate as dates:
Dim sdate As Date
Dim Ldate As Date


                        
这篇关于Excel VBA  - 日期格式自动更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！