C# data output format

Shane Nicholson

Good afternoon all,

As always, thank you in advance for your help.

I have made a windows form in VS 2019 and have a datetimePicker item. The form data is saved in CSV format and then used in Tableau.

The date is stored as ddMMyyyy when saved which is a huge problem. For example, If saved data on 5th Aug 2020, I want it to save it as YYYYMMDD (2020-08-05) but it saves it as 05-08-2020. When I open the saved file, it will show the date as May 08 2020.

I simply want the date format to save as YYYY-MM-DD..

private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
{
dateTimePicker1.Format = DateTimePickerFormat.Custom;
dateTimePicker1.CustomFormat = "yyyyMMdd";
}
Private void textbox8_Textchanged(object sender, EventArgs e)
xlworksheet.Cells[1, 1] = textBox1.Text;
xlworksheet.Cells[1, 2] = dateTimePicker1.Value;

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta5y ago
Accepted answer
@Shane

Created a small peice a code for you that solves your issue (as stated in earlier post to create a format for the library you are using (i.e Interop.excel).
// namespace used
using System;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
using System.IO;

void SaveAsCSV()
        {
            Application xlap = new Application();

            // if it set to false then after exiting your application a process of Microsoft.Excel keeps running
            // in the background that consumes system resources
            xlap.UserControl = true;

            Workbook xlworkbook = xlap.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet xlworksheet = (Worksheet)xlworkbook.Worksheets[1];

            SetDateFormat(xlworksheet, 1, 2);

            xlworksheet.Cells[1, 1] = "first value";
            xlworksheet.Cells[1, 2] = DateTime.Now;

            var dir = AppContext.BaseDirectory;
            var file = Path.Combine(dir, "excel_" + DateTime.Now.ToString("yyyyMMddHHmmss")) + ".csv";
            xlworkbook.SaveAs(file, XlFileFormat.xlCSV);
            xlworkbook.Close(false);

            xlap.Quit();
        }

void SetDateFormat(Worksheet ws, int row, int col, string format = "yyyy-MM-dd")
        {
            ((Range)ws.Cells[row, col]).EntireColumn.NumberFormat = format;
        }
Output:

BTW, it's a csv file (basically a text file seperated by a comma), which really doesn't require any library to save it, infact Interop.excel is having some cons like (needs excel to have installed in the target computer alongwith slow performance issue), you can save it using any TextWriter in C#.

Update:
If you are looking in excel then the format might be different because it converts the format accoring to system environment date. Don't just open the csv file in excel, instead import the csv file and you will get your desired output and ofcourse doesn't required any library/function to convert the format

Do let me know, if it resolves your issue!
Shane Nicholson5y ago
Good afternoon Ben,

So I tested what you mentioned and I am getting the file as MONTH/DAY/YEAR now...
I changed the format to yyyyMMdd and removed it completely and still get the same outcome.
There is no way in my code that specifies the date output to be MMddyyyy, its so strange.
Please keep your suggestions coming..

Thank you
Ben Thompson5y ago
You have this Event Handler in the code you pasted:
private void textBox11_TextChanged(object sender, EventArgs e)
{
textBox11.Text = DateTime.Now.ToString("dd-MM-yyyy");
}
So it's always going to be shifted to "dd-MM-yyyy" format. Either remove the event handler or modify it so the changed text is formatted the yyyyMMdd you prefer.
Amit Gupta5y ago
There is no chance, the solution won't work. Recommend you to remove the reference if interop and take a fresh reference, clean and rebuild your project.
Shane Nicholson5y ago
Good morning Amit,

I tried that too. I added it at the beginning of the code after creating a new xlworksheet, still the same format. I cannot believe how diffiuclt something like this is..
Amit Gupta5y ago
Try setting formatting like Range rg = (Excel.Range)worksheetobject.Cells[1,2]; rg.EntireColumn.NumberFormat = "yyyy-MM-dd";https://stackoverflow.com/a/3310837
Shane Nicholson5y ago
Morning All,

Thank you all for taking the time to reply. Its relly nice to know there are so many knowledgeable people out there who are willing to help.

I have provided the entire code below for more information. The document still saves in the format ddMMyyyy with the current code, its becoming frustrating.

I removed the dattimepicker and replaced it with textBox11 to try and make it easier.

Best regards..
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
using System.Runtime.ExceptionServices;
using System.Globalization;
namespace Navigations_delivery_report
{
public partial class Form1 : Form
{
Excel.Application xlap = new Excel.Application();
public Form1()
{
InitializeComponent();
textBox1.Text = Environment.UserName;
textBox11.Text = DateTime.Now.ToString("yyyy-MM-dd");
textBox10.Enabled = false;
comboBox1.Items.Add("SLT AVOIDANCE");
comboBox1.Items.Add("MTT");
comboBox1.Items.Add("MCT");
comboBox1.Items.Add("MFT");
comboBox1.Items.Add("MDT");
comboBox1.Items.Add("DEFRTE");
comboBox1.Items.Add("PRED");
}
private void label1_Click(object sender, EventArgs e)
{
}
private void label5_Click(object sender, EventArgs e)
{
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
string result = "Please Enter ";
if (textBox1.Text == string.Empty)
{
result += " Name,";
}
if (textBox2.Text == string.Empty)
{
result += " CallSign,";
}
if (textBox3.Text == string.Empty)
{
result += " Departure,";
}
if (textBox4.Text == string.Empty)
{
result += " Arrival,";
}
if (textBox5.Text == string.Empty)
{
result += " Fuel,";
}
if (textBox6.Text == string.Empty)
{
result += " Cost,";
}
if (textBox7.Text == string.Empty)
{
result += " Time,";
}
if (comboBox1.SelectedItem == null)
{
result += " Type Of Route,";
}
if (comboBox2.SelectedItem == null)
{
result += " Route Method,";
}
if (checkBox1.Checked == false)
{
result += " Confirm OFP Matches ATC Plan,";
}
else
{
if (result.Contains(','))
{
result = result.Remove(result.LastIndexOf(','));
}
}
if (result != "Please Enter ")
{
MessageBox.Show(result);
}
else
{
Excel.Workbook xlworkbook;
Excel.Worksheet xlworksheet;
object misValue = System.Reflection.Missing.Value;
xlworkbook = xlap.Workbooks.Add(misValue);
xlworksheet = (Excel.Worksheet)xlworkbook.Worksheets.get_Item(1);
Excel.Sheets worksheets = xlworkbook.Worksheets;
worksheets[3].Delete();
worksheets[2].Delete();
xlworksheet.Cells[1, 1] = textBox1.Text;
xlworksheet.Cells[1, 2] = textBox11.Text = DateTime.Now.ToString("yyyy-MM-dd");
xlworksheet.Cells[1, 3] = textBox2.Text;
xlworksheet.Cells[1, 4] = textBox3.Text;
xlworksheet.Cells[1, 5] = textBox4.Text;
xlworksheet.Cells[1, 6] = textBox5.Text;
xlworksheet.Cells[1, 7] = textBox6.Text;
xlworksheet.Cells[1, 8] = textBox7.Text;
xlworksheet.Cells[1, 9] = textBox8.Text;
xlworksheet.Cells[1, 10] = textBox9.Text;
xlworksheet.Cells[1, 13] = textBox10.Text;
xlworksheet.Cells[1, 11] = comboBox1.Text;
xlworksheet.Cells[1, 12] = comboBox2.Text;
xlworkbook.SaveAs(@"\\172.21.4.14\BusinessLanding\BusinessLanding_Sync\operations_sync\FlightPlanning\savings_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv", Excel.XlFileFormat.xlCSVWindows);
xlworkbook.Close(true);
xlap.Quit();
textBox2.Clear();
textBox3.Clear();
textBox4.Clear();
textBox5.Clear();
textBox6.Clear();
textBox7.Clear();
textBox8.Clear();
textBox9.Clear();
textBox10.Clear();
comboBox1.SelectedIndex = -1;
comboBox2.SelectedIndex = -1;
checkBox1.Checked = false;
textBox2.Focus();
}
}
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
}
private void button2_Click(object sender, EventArgs e)
{
Close();
}
public void Button3_Click(object sender, EventArgs e)
{
textBox2.Clear();
textBox3.Clear();
textBox4.Clear();
textBox5.Clear();
textBox6.Clear();
textBox7.Clear();
textBox8.Clear();
textBox9.Clear();
textBox10.Clear();
comboBox1.SelectedIndex = -1;
comboBox2.SelectedIndex = -1;
checkBox1.Checked = false;
textBox2.Focus();
}
private void textBox8_TextChanged(object sender, EventArgs e)
{
}
private void textBox9_TextChanged(object sender, EventArgs e)
{
}
private void button4_Click(object sender, EventArgs e)
{
}
private void textBox6_TextChanged(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(textBox6.Text) && textBox6.Text != "-")
{
double firstboxS;
double SECONDS;
double answerS;
var format = new NumberFormatInfo();
format.NegativeSign = "-";
format.NumberDecimalSeparator = ".";
firstboxS = double.Parse(textBox6.Text, format);
SECONDS = double.Parse(textBox8.Text, format);
answerS = firstboxS - SECONDS;
textBox9.Text = (answerS.ToString());
}
}
private void textBox5_TextChanged(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(textBox5.Text) && textBox5.Text != "-")
{
double firstbox;
double answer;
var format = new NumberFormatInfo();
format.NegativeSign = "-";
format.NumberDecimalSeparator = ".";
firstbox = double.Parse(textBox5.Text, format);
answer = firstbox / 1000 * 3.18141 * 2.3 * 1.19;
textBox8.Text = (answer.ToString());
}
}
private void button4_Click_1(object sender, EventArgs e)
{
}
private void label7_Click(object sender, EventArgs e)
{
}
private void label12_Click(object sender, EventArgs e)
{
}
private void textBox7_TextChanged(object sender, EventArgs e)
{
}
private void textBox10_TextChanged(object sender, EventArgs e)
{
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
int selectedIndex = comboBox1.SelectedIndex;
if (selectedIndex != -1)
{
object selectedItem = comboBox1.SelectedItem;
if (selectedItem.ToString() == "SLT AVOIDANCE")
{
textBox10.Enabled = true;
}
else
{
textBox10.Enabled = false;
}
}
}
private void textBox11_TextChanged(object sender, EventArgs e)
{
textBox11.Text = DateTime.Now.ToString("dd-MM-yyyy");
}
}
Amit Gupta5y ago
Which library you are using for xlworksheet?
If all stuffs doesn't work then you neeed to change the format in that cell or column.

Do recheck that and let us know, if it resolves the issue
Ben Thompson5y ago
If you just want the string representation, you can use what Sachin mentioned. However, the dateTimePicker1.Value is already a DateTime object so the cast isn't necessary.
DateTimePicker picker = new DateTimePicker();

string date = picker.Value.ToString("yyyy-MM-dd");

The upper-case "MM" indicates month. The microsoft docs have more info on custom datetime strings.

https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

Since you're using a custom string to unpack this into a new DateTime object, it'll need to be parsed, and you'll need to instruct the parser exactly what values in the string represent which parts of the datetime:

DateTime dt = DateTime.ParseExact(date, "yyyyMMdd", CultureInfo.InvariantCulture);
Shane Nicholson5y ago
Hi Sachin,

Thank you for taking the time to reply. The ((dateTime) part didnt work. I think I have figured it out anyway. But as always, much appreciated...
Sachin Singh5y ago
You can use ToString() like below
xlworksheet.Cells[1, 2] = ((dateTime)dateTimePicker1.Value).ToString("yyyy-mm-dd");