How to make correct date format when writing data to Excel
Did you try formatting the entire column as a date column? Something like this:
Range rg = (Excel.Range)worksheetobject.Cells[1,1];
rg.EntireColumn.NumberFormat = "MM/DD/YYYY";
The other thing you could try would be putting a single tick before the string expression before loading the text into the Excel cell (not sure if that matters or not, but it works when typing text directly into a cell).
Converting to Excel Date format (within Excel file) using python and pandas from another date format from html table
You need to use pd.ExcelWriter
to create a writer
object, so that you can change to Date format WITHIN Excel; however, this problem has a couple of different aspects to it:
- You have non-date values in your date column, including "Legend:", "Cash rate decreased", "Cash Rate increased", and "Cash rate unchanged".
- As mentioned in the comments, you must pass
format='%d %b %Y'
topd.to_datetime()
as that is the Date format you are converting FROM. - You must pass
errors='coerce'
in order to returnNaT
for those that don't meet the specified format - For the
pd.to_datetime()
line of code, you must add.dt.date
at the end, because we use adate_format
parameter and not adatetime_format
parameter in creating thewriter
object later on. However, you could also excludedt.date
and change the format of thedatetime_format
parameter. - Then, do
table = table.dropna()
to drop rows with any columns withNaT
- Pandas does not change the Date format WITHIN Excel. If you want to do that, then you should use
openpyxl
and create awriter
object and pass thedate_format
. In case someone says this, you CANNOT simply do:pd.to_datetime(table['Effective Date'], format='%d %b %Y', errors='coerce').dt.strftime('%m/%d/%y')
or.dt.strftime('%d/%m/%y')
, because that creates a "General" date format in EXCEL. - Output is ugly if you do not widen your columns, so I've included code for that as well. Please note that I am on a USA locale, so passing
d/m/yyyy
creates a "Custom" format in Excel.
NOTE: In my code, I have to pass m/d/yyyy
in order for a "Date" format to appear in EXCEL. You can simply change to date_format='d/m/yyyy'
since my computer has a different locale than you (USA) that Excel utilizes for "Date" format.
Source + More on this topic:
import pandas as pd
import html5lib
import datetime
import locale
import pytz
import lxml as lx
import openpyxl as oxl
url = "https://www.rba.gov.au/statistics/cash-rate/"
tables = pd.read_html(url)
table = tables[0]
table['Effective Date'] = pd.to_datetime(table['Effective Date'], format='%d %b %Y', errors='coerce').dt.date
table = table.dropna()
table.to_excel('rates.xlsx')
writer = pd.ExcelWriter("rates.xlsx",
engine='xlsxwriter',
date_format='m/d/yyyy')
# Convert the dataframe to an XlsxWriter Excel object.
table.to_excel(writer, sheet_name='Sheet1')
# Get the xlsxwriter workbook and worksheet objects in order to set the column
# widths, to make the dates clearer.
workbook = writer.book
worksheet = writer.sheets['Sheet1']
worksheet.set_column('B:E', 20)
# Close the Pandas Excel writer and output the Excel file.
writer.save()
C# Excel changes the date format when I write a date in a worksheet
have you tried passing your date using the Format class? Note that passing the ' in before the value will tell excel to format it as text and it shouldn't try and do its own formatting.
mWSheet1.Cells[row , 7] = "'" + Format("01/12/2019", "MM/dd/yyyy");
Persisting the date format in excel while fetching data
Thanks Naveen for your question, we actually are building a new API for this scenario. This API is now in beta preview. This API is targeting to release in 1.12 which would be GA in this fall.
API range.numberFormatCategories
which represent the category of number format of each cell of the range. it will return the categories in
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
sheet.activate();
let range = sheet.getRange("A1:C3");
range.load("address");
range.load("numberFormatCategories");
await context.sync();
console.log("address: " + range.address);
console.log("NumberFormatCategory: " + range.numberFormatCategories);
});
Here is the document
https://learn.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#numberformatcategories
You can try this gist in Windows and Excel online. https://gist.github.com/lumine2008/475327d889031ced8daf4a87b08c832c
R code incorrectly writing date format to excel
I'm not sure I understand the question but to format dates you could try the dmy
(day month year) function in lubridate, or specify a format to as.Date
library(lubridate)
lubridate::dmy("11/10/2015")
as.Date("11/10/2015", format = "%d/%m/%Y")
Have a look at ?strptime
to get information on other formats.
You can also output a date in a particular format
x <- as.Date('2011-10-11')
format(x, format = '%d/%m/%Y')
[1] "11/10/2011"
Date column won't format correctly in Excel
Select column and go Data > TextToColumns > Delimited > Next > Next
and choose an import date format of YMD (in your case MDY) > Finish
IF further changes are required:
Set the formatting of the column via right-click > Format Cells > Number Tab > Custom
and type "YYYYMM" or whatever you need.
Why Date format gets change while exporting data to Excel?
There are two very different things here:
- How the DateTime is stored
- How the DateTime is displayed
.NET side
.NET stores DateTime as the number of Ticks since start of the Unix Epoch. Every other property and the String Representation are a interpretation of this value.
.NET Displays any numeric value - including DateTime - by extracting the users Culture settings from Windows. This is a very usefull feature, as that is one big part we do not generally have to take care off.
Excel Side
The old Excel Format uses Decimal or float value. The part before the Decimal Seperator is the deys since 0-jan-1900. It also has a well known mistake, treating 1900 as a Leap Year. The parts after the seperator denote the time of the day. But it might still have seperate types for date and time (these fell out of favor, for being almsot useless in pracitce).
How it displays those values is entirely up to the Excel Version you are viewing it in and the settings it is set to.
How do I keep the date-format from Excel when importing a time series in R?
I would use one of these functions:
xlsnum_to_POSIXct <- function(n)
{
as.POSIXct(
x = n*24*60*60,
origin = "1900-01-01 00:00:00"
);
}
xlsnum_to_POSIXlt <- function(n)
{
as.POSIXlt(
x = n*24*60*60,
origin = "1900-01-01 00:00:00"
);
}
which converts the number of days into number of seconds since 1900/01/01, and wraps it conveniently into an R date-time type.
Should work also with fractions of day, vectors of day counts etc.
Related Topics
Accuracy of Textrenderer.Measuretext Results
Check If Datetime Instance Falls in Between Other Two Datetime Objects
How to Disable Visual Styles for Just One Control, and Not Its Children
Is .Net 4.0 Compatible with Windows Xp Sp2 or Below
Process.Start to Open an Url, Getting an Exception
Regular Expression to Get the Src of Images in C#
Is Deferred Execution in ASP.NET MVC View a Very Bad Thing
Calculate a Point Along the Line A-B at a Given Distance from A
Client Id for Property (ASP.NET MVC)
Why am I Getting Sehexception When Calling Roleenvironment.Getconfigurationsettingvalue("Mykey")
How to Use Multi Color in Richtextbox
Registry.Localmachine.Opensubkey() Returns Null
Azure Key Vault: Access Denied
How to Generate and Auto Increment Id with Entity Framework
Mocking Static Methods Using Rhino.Mocks
Using Graphics.Drawimage() to Draw Image with Transparency/Alpha Channel