Tag: date

format an excel column to accept date in particular format only phpExcel

I am generating an excel sheet via phpExcel in which dates are retrieved in 01-JAN-1900 format.But I want it to be visible in DD-MM-YYYY format right from when the excel sheet is generated with default date values fetched from DB.Also the date format is DD-MM-YYYY in DB.So how can we format that cell to display […]

Convert Java date to Excel date results in one more day than actual, before March 1, 1900

I am using POI to export some data into an Excel file. I have some code to insert some date into Excel sheet cell as date (rather than text). I works fine for dates after 01/03/1900. However for dates before that, it results a dates one more day than actual day. //SOME CODE final HSSFDataFormat […]

vbscript assign date variable to .xls cell with UK date

Struggling to find an answer to this one. To cut a long story short, I work in a case management system that is developed in it’s own syntax, although can execute VBScript through a native command. I have written code in VBS to export some data from the system to a template xls file but […]

Separate date and time in Excel

I need to separate the date and time for a large list of data in Excel, the format is 12/23 1600 (i.e. 23 December 16:00). I tried the function =DATE(YEAR(serial_number),MONTH(serial_number),DAY(serial_number)), but it couldn’t read that format. Could you please tell me how to do that without change the format?

Hide column in excel by date YYYY MM for the rest of this year plus three more

I’m trying to hide columns in excel with vba. So far This is what I have it will hide after 36 months but I was just told that it needed to do the rest of the forecasted year plus three years then hide all months in year 4 and 5 except for December. I added […]

Excel: Array to List (Combine time columns with date rows)

In excel, I have a table with dates as rows and hours (0-24) as columns, and values at their intercept. I would like to change this array (365 by 24) to a list such that: Monday Hour1 value; Monday Hour2 value; Monday …; Monday Hour24 value; Tuesday Hour1 value; Tuesday … ; Tuesday Hour 24 […]

Python xlrd fails on reading dates to create CSV only in SOME cases

I’ve created a XLS2CSV function using xlrd and I do not know exactly what column may be used for dates in the .xls input file. The following code is working almost ok, but in some cases (and not always the same rows “fails” if you run it many times with the same input XLS file) […]

Compare unknown time stamps in VBA?

I have worksheets with a timestamp in the format of MM/DD/YYYY hh:mm:ss I want to be able to copy the row on which two timestamps from two different worksheets match, with a +1-second error margin. So if I have a timestamp with time 12:14:13, it will find a match with a timestamp of time 12:14:13 […]

xlsx to csv: date format changed from DD.MM.YYYY to DD.MM.YY

I get despaired so I hope someone can help me. I am exporting an Excel sheet (.xlsx) to a CSV file (.csv). The table contains various types of information including some dates. The dates are formatted as DD.MM.YYYY in the Excel sheet. However when performing the export to a CSV file (File> Save as> CSV […]

Excel – Datevalue to equal another cell's datevalue, but show up in formulas as that datevalue and not =CELL

I’m trying to get two spreadsheets more compatible for ease of copy and pasting. In my Master spreadsheet, where I’ll be pasting everything, all the formulae for dates and times are in DateValue. So when I click on the option to ‘show formula’, DateValue numbers will show. In normal view, they obviously show the date […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.