How to Make Correct Date Format When Writing Data to Excel

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:

  1. You have non-date values in your date column, including "Legend:", "Cash rate decreased", "Cash Rate increased", and "Cash rate unchanged".
  2. As mentioned in the comments, you must pass format='%d %b %Y' to pd.to_datetime() as that is the Date format you are converting FROM.
  3. You must pass errors='coerce' in order to return NaT for those that don't meet the specified format
  4. For the pd.to_datetime() line of code, you must add .dt.date at the end, because we use a date_format parameter and not a datetime_format parameter in creating the writer object later on. However, you could also exclude dt.date and change the format of the datetime_format parameter.
  5. Then, do table = table.dropna() to drop rows with any columns with NaT
  6. Pandas does not change the Date format WITHIN Excel. If you want to do that, then you should use openpyxl and create a writer object and pass the date_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.
  7. 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()

enter image description here

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

enter image description here

here is the sample code for this API

  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:

  1. How the DateTime is stored
  2. 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



Leave a reply



Submit