Tag Archives: date

Ignoring Time Value from DateTime cells – MS Excel

Questions: I have following date values (with time) in Column A. 1/1/2012 8:50 1/1/2012 8:45 1/1/2012 8:55 1/1/2012 8:59 1/1/2012 8:12 1/1/2012 8:30 1/1/2012 9:50 1/1/2012 10:00 And the following value in Cell B1. 1/1/2012 Now, when I apply formula =COUNTIF(A:A,”=” & B1) to count the number of cells in Column A matching Cell B1,… Read More »

Change the date to fiscal year

Questions: I want to write a function in Excel to change the date. The logic is like this: if the month is (Jan, Feb or March) the result show me one year past (-1 year) and if the month is (April to -December) the result show the current year (which year the date shows). example:… Read More »

Using Excel to generate SQL – problem handling date field

Questions: I have an excel file filled with data my customer wants placed into a database. I’ve generated a column along the left of the data that looks like this (obviously changed the field names for here): =”INSERT INTO TBL_CUSTOMER (FIELD, FIELD, FIELD, FIELD, FIELD, FIELD, FIELD, FIELD, FIELD, FIELD, FIELD, FIELD, FIELD, FIELD, FIELD,… Read More »

Excel: Extract Numbers from Date Strings

Questions: I have several malformed date columns. I am trying to transform these columns into “month”, “year”, and “start_day” columns. Here is an example sample: January 13th, 2018 January 13th-14th 2018 January 5th-7th 2018 January 4th-8th 2018 December 9th-10th 2017 December 2nd-3rd 2017 December 2nd, 2017 December 2nd-3rd 2017 November 18th, 2017 November 18th-20th, 2017… Read More »

PHPExcel Reader Date Format Issue – rangeToArray

Questions: I am facing a Date format issue when reading excel using PHPExcel. Even I used $objReader->setReadDataOnly(false); getting format of the cell but not working for me. This how I am reading the data from Excel. $get_excel_data = $objPHPExcel->getActiveSheet()->rangeToArray(“A1:”.$highestColumn.$highestRow); When I am trying to print date, it shows like 03/06/13, and I have tried to… Read More »

Removing time element of Long Date Format

Questions: I have a list of data within the last three years, but all the dates have different times on it. I am trying to write a macro that will delete all the times. I tried changing the format of the cell but it didn’t work. EX. the list looks like: 10/12/2011 08:41 PM Eastern… Read More »