Can't get date formatting right when writing to worksheet

JONeill

Board Regular
Joined
Sep 2, 2018
Messages
58
I have a form that uses date picker controls for a beginning and ending date. I have a listbox that uses the rowsource property to display columns on the data. Whenever I write to the worksheet, the dates display as numbers. Even when I change the cell formatting to short date, then next write changes the format. Both date pickers are set to short date. The immediate window shows valid formats but not after writing to the cells. Here is some code.


The first set of code calls the module that actually writes the collection to the worksheet.
Code:
 Set mLog = Factory.CreateMaintLog(Trim(txtProjectName.Text), _
                                            cboLocation.Value, _
                                            dtStartDate.Value, _
                                            dtCompletionDate.Value, _
                                            chkCompleted.Value, _
                                            Trim(txtDescription.Text), _
                                            Trim(txtMaterials.Text))
                                            
'Add item to collection and write to Data worksheet.
colMaintLogData.Add mLog
IData.WriteMaintLogData maintLogParms, colMaintLogData



This code writes to the worksheet...



'Set first row
row = DRngPrms.firstRow
With Sheets(DRngPrms.ActiveDataSheet)
 'Delete current data in spreadsheet
 Range(DRngPrms.DataRange).Clear
 'Write data for each row
 For Each mLog In colData
  .cells(row, colNames.ProjectName).Value2 = mLog.ProjectName
  .cells(row, colNames.Location).Value2 = mLog.Location
  .cells(row, colNames.StartDate).Value2 = mLog.StartDate
  .cells(row, colNames.CompletionDate).Value2 = mLog.CompletionDate
  .cells(row, colNames.Completed).Value2 = mLog.Completed
  .cells(row, colNames.Description).Value2 = mLog.Description
  .cells(row, colNames.Materials).Value2 = mLog.Materials
  row = row + 1
 Next mLog
End With
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try:

Code:
[COLOR=#333333].cells(row, colNames.StartDate).[/COLOR][COLOR=#0000ff]Value [/COLOR][COLOR=#333333]= [/COLOR][COLOR=#0000ff]cdate[/COLOR][COLOR=#333333](mLog.StartDate)[/COLOR]
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
Hello JONeill,

Try removing .Value2 from:-

Code:
.Cells(Row, colNames.StartDate).Value2 = mLog.StartDate

In brief, dates are stored as numbers so Value2 will find the underlying value of a cell. Value2 will not check the cell format and convert it to a date (as you would read it). Its pretty well much the same when using just Value. Just some minor differences.

I hope that this helps.

Cheerio,
vcoolio.
 

JONeill

Board Regular
Joined
Sep 2, 2018
Messages
58
Removing the Value2 seems to have done the trick. I had heard that Value2 was designed as a "patch", if you will, to Value and the preferred way was to use it. Seems work fine now. Thanks!
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
You're welcome. I'm glad to have been able to help.

Cheerio,
vcoolio.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,632
Messages
5,523,994
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top