Macro: European & US Date Formats

Nov 20, 2006

An in-house server spits out a whole lot of dates which I put into Excel and then use the dates for analysis (VBA code). The problem is that Excel is getting confused about the date formats. Sometimes the dates are formatted as dd/mm/yyyy and then the next line can randomly be mm/dd/yyyy. This totally messes up all changes of having data integrity.

View 5 Replies


ADVERTISEMENT

Stop European Date Format Changing

Jan 21, 2008

I have a userform which is used to update a spreadsheet.

There are 7 textboxes which add dates to the spreadsheet. These dates are then used in formulas.

Every time a record is saved the date format changes.

Ths means that if the date is entered dd/mm/yyyy it writes mm/dd/yyyy and this is fine. When this record is next retrieved it will show mm/dd/yyyy and any new date will be dd/mm/yyyy.

Wen saved to the spreadsheet both formats change meaning the dates in the spreadsheet appear in diffrent formats messing up all formulas.

How can I stop the date format changing each time the record is saved?

View 3 Replies View Related

Collect InputBox Date In European Format

Mar 4, 2008

I am trying to get the user to enter a relevant date but code returns date in US format. That is mm dd yyyy instead of dd mm yyyy.

View 5 Replies View Related

Macro To Convert Date Formats In Two Columns And Compare

Sep 15, 2014

I have two columns containing dates (Date1 and Date2). Date1 is like a long date and Date 2 is a short date. I need a macro to compare these two dates and delete rows where Date1 <> Date2. Please find attached the before and after file which also contains the date formats for these two dates.

View 8 Replies View Related

Pasted European Date Format Coverting To US Format

Mar 19, 2008

I have a problem where i am just trying to do a simple copy of dates from one spreadsheet to another

18/03/2008 00:00 however when the its copied to the other spreassheet is changes to the US date formate 3/18/2008 0:00 I need to keep the format as date as the date needs to be the current day. How do i get this to remain UK

View 5 Replies View Related

Extract Data With Differing Date Formats - Convert Text To Date

Mar 27, 2013

Extract data with differing date formats that I need to convert to something consistent that I can format as a date.

This is an example of data.... all with general format at the moment.

2/28/2013 2:48:53 PM

1/16/2013 10:48:50 AM

12/17/2012 11:59:49 AM

I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?

=LEFT(G9,SEARCH("/",G9)+7)

(The day portion of this date always has a leading zero).

View 9 Replies View Related

Convert Different Date Formats Into One Date Format?

Apr 15, 2013

I have a worksheet where in a column, dates are stored in various date format i.e. it may be DMY or MDY or YMD.

08-06-12

08-06-12

08-07-12

08-07-12

13/08/2012

13/08/2012

13/08/2012

14/08/2012

Above is just for an example, above dates are for the month of August, but as can been some cells are in MDY formate and some in DMY. Can I have formula to convert different date formats into one date format?

View 9 Replies View Related

US To UK Date Formats

Oct 21, 2009

I'm running excel 2000 and don't have access to mscal.ocx, so i have used the date time picker with a calendar, but the formatting of the calendar is in US format m/d/yy and I need it in UK format d/m/yy, I have narrowed it down the following piece of code, the D values are the days,

View 12 Replies View Related

Various Date Formats In Same Column

Jun 22, 2014

I need for my office a table which has one column with dates. The "issue" is, that sometimes I know the whole date (day, month, year), sometimes only the month and year and occasionally just the year. Is there a way through custom number formatting that excel behaves correctly? How would I need to set conditions to achieve this? Because now something happens which is 99% incorrect .

View 6 Replies View Related

Different Date Formats On The Same Spreadsheet

Mar 13, 2007

I have data sent to me with different date formats on the same spreadsheet; I used Format - Cell and format date to this format: yyyy.mm.dd. However, only some of the data changed to this format and rest remain the same. I've tried many other ways, but didn't work.

View 14 Replies View Related

Change Date Formats

May 28, 2009

I have a userform which allows data to be entered onto a worksheet. One of the fields (a textbox with the standard calendar control) is the date. When the transaction saves, it correctly saves the date as dd/mm/yyyy using

ActiveCell.Value = TxtDate.Text

I also have a form which allows the user to amend a transaction by loading the data onto the form, amend it and save it back to the worksheet.

The problem is that when it picks up the data and re-saves it, it's resaved in the format mm/dd/yyyy which is a problem because I use the month as one of the categories that users can filter the data by.

I also have the following line immediately after each time the data is saved from the form to the worksheet:

Selection.NumberFormat = "dd/mm/yyyy;@"

View 7 Replies View Related

Number Formats For Date

Jan 26, 2010

I have a simple piece of code that gets a date from the user via inputbox in the format dd/mm/yyyy. That date is then put into a cell (e.g. B1), and the cells to its right get given something like "=B1+2", "=C1+5" etc so that they will all show a date a few days further down the track. (Much more complicated than that but this is the basic idea)

The entire row is then formatted "ddd dd mmm yyyy" with the intention of every column getting a header like "Wed 27 Jan 2010". This works perfectly for every column EXCEPT B which shows the the string "27/01/2010" it was given from the inputbox.

Right clicking --> Format Cells shows every cell in the row has the exact same 'Custom' format, and the 'Sample' at the top looks correct for every cell except B1.

BEFORE the macro runs and formats all the cells, but AFTER they have been given their formulas, they all display something along the lines of '40021', '40023' which I assume is the number of days since Jan 01 1900 or the like - proving that THEY knew the value in B1 was a date, so why doesnt B1 itself know?

View 2 Replies View Related

Date Formats From Useform?

Mar 1, 2012

I have a userfiorm with a text box that auto fills as follows

PHP Code:

Fill Datebox    DateBox.Value = Format(Date, "dd/mm/yyyy") 

which today fills the text box as 01/03/2012 (UK format), however when i try log a record as follows

PHP Code:

copy the data to the databasews.Cells(iRow, 1).Value = Me.DateBox.Value 

it changes the date to the american format so that it is pulling through on the data base as today being the 3rd Jan 2012.

View 2 Replies View Related

Change The Date Formats

May 9, 2008

I am importing data (using filestream) from txt files with comma delimited values. When I import dates into General type cells it turns a date of dd/mm/yyyy into mm/dd/yyyy only if the date makes sense.

I would like it to import dd/mm/yyyy as dd/mm/yyyy.

I am also interested to know how Excel/VBA deals with dates coming from various sources, what quirks or mode of thought it follows.

View 9 Replies View Related

Converting Date Formats Depend On Value

Mar 10, 2009

I need to amend that column to display 26 Mar ( or 12 Dec or 17 Apr etc depending on value).

Try as I might, using format cells, nothing results in the required display. I'm not even sure what format the cells were in as received, since highlighting the received column and selecting "format cells" does not reveal the initial formatting. If I try pasting special to a new column and selecting values, I just get the numerical date value as a 5digit number.

View 6 Replies View Related

Reformat The Date & Time Formats

Dec 1, 2009

I've attached a small sample of an excel file I created from a CSV. The date has defaulted to American format and the time is, as you can see, messy. How can I reformat so that I can have British date format and 24 hour clock?

View 2 Replies View Related

Chart Showing Date Formats

Jul 20, 2012

I have a spreadsheet that has two columns. Column A is a date format and column B has the number of days between both dates over 2 years analysis is =A8-A7.

However when i come to build a chart its all wrong as the dates are following a weekely format, can i adjust this to pick up only the dates that are showing on the cells in column A? also is ther ea way which teh #VALUE! doe snot show?

02/07/2012

14/08/2012
43

00/00/0000
#VALUE!

[Code]...........

View 2 Replies View Related

Date Formats In A Cell Which Also Contains Text

Nov 29, 2013

I need to change the date format in a cell which also contains text.

The full formula is as follows:-
="Report period:
"&TEXT(Control!B9,"dd mmmm yyyy")&" - "&TEXT(Control!B10,"dd mmmm yyyy")

This gives me the following:-
Report period:
01 October 2012 - 30 September 2013

What I would like to have is the following:-

Report period:
1st October 2012 - 30th september 2013

It's only a minor change but I think it looks better. Without a hideously complicated formula, if this is possible using one of the standard cell formats?

View 4 Replies View Related

SUMIF With Different Date Formats (Google Sheets)

Aug 9, 2014

I have data which is exported from my system which includes the job date and gross profit amount. I want to find the gross profit total for each day based on a matching date. However, the data exported includes the time in the date cell and Google Sheets won't match it. I'd like to avoid using a helper cell if at all possible.

You can see the formula here : [URL] ....

Formula is on the Q column.

View 1 Replies View Related

Date Formats Incorrect When VBA Opens Workbook

Apr 29, 2008

I'm new to VBA so my i'm having alot of problems figuring out simple stuff.

Below is my script and when i run it, the dates turns out incorrect.

I noticed this only happens to dates that are before 13th of each month.

Example,

1st May 2008 ( 01/05/2008)

will turn up as

5th Jan 2008 ( 05/01/2008)

However when i manually open the file Todays trades.csv
The date looks just fine.

Is there some problem with using VBA to call up the file?

View 9 Replies View Related

Retrieving Various Date Formats And Return These Into Cell

Nov 17, 2011

I am trying to find dates & time within text in a cell & return to a separate cell. The issue I have is that the date format varies frequently. I also can't rely on searching for "Sent:" as this also varies frequently

e.g. From: ########
Sent: 17 November 2011 11:57

I would like to return 17/11/2011 11:57

From: #######
Sent: 01/11/2011 11:50:13

I would like to return 01/11/2011 11:50

From: ########
Date: 05/11/2011 09:45:13

How can i search for various forms of dates and return them into a cell?

View 2 Replies View Related

Date Formats Not Picked Up In Pivot Table

May 16, 2012

I have inherited a spreadsheet that I need to update soon. It takes data that has a date field and creates a pivot table by month. The person who created it completed it thru February. Now I have added data for March and April, but the pivot tables can not find this new data and I suspect it is because of the date formatting (I have done the Change Data Source under PivotTable Options to include all the new data).

The reason I suspect the date format is that she built a button to run a macro named "dateformat", which is not in the workbook. I have tired to manually format the date using the format painter from the February dates that the pibvot table recognizes, but I still get no results.

View 4 Replies View Related

Remove European Currency Sign

May 2, 2008

I have column with thousand of line with european currency sign at the end. The excel wont add em cuz these sign are not $ sign. How can I eliminate the sign at the end so that I able to add up?

View 9 Replies View Related

Converting American Dates To European - Non-formatting

Jan 27, 2007

I get sent a spreadsheet by one of our partners where the dates look like they are in European date format except thay are actually in American format.

example

01/09/2007 - they mean 9th January 2007 but Excel holds 39326 which is 1st Sept 2007.

I can obviously format them but when I sort the list Excel still reads 1st Sept instead of 9th Jan

There are over a thousand entries so re-typing isn't an option.

View 9 Replies View Related

Macro For More Than Three Conditional Formats

Oct 12, 2008

I use Excel 2003 and am trying to put together an automatic macro to apply five conditional formats to a range of cells. The range is H3:H21 and is unnamed. The values in the cells are derived from formulas. These are the conditional formats:

If there is a 1, the background color should be gold/44 and the font should be bolded.
If there is a 2, the background color should be gray 25%/15 and the font should be bolded.
If there is a 3, the background color should be dark yellow/12 and the font should be bolded.
If there is an e, the background color should be red/3 and the font should be bolded.
If there is a 0 (zero), the font should be white/2.

From what I understand, I need to put the code in by using the "View Code" option in the tab sub-menu.

View 10 Replies View Related

Macro Automation Even When Formats Change

Jun 4, 2014

I receive sales data from my wholesalers every month and I continually have to format them to fit the structure of our in-house database. I wanted to design a macro that would automate this process. However, in some months, the files are recieved in a format that is a bit different from the wholesaler's usual format.

Is there such thing as an initial "litmus" test where I could try running the macro and if it doesn't fit the usual structure, there's an error code and I could do it by hand?

View 3 Replies View Related

Macro To Formats Five Worksheets In The Same File

Jul 27, 2006

I did this macro which formats five worksheets in the same file. I saved this macro as a PERSONAL.XLS file. Last night when i open new files to re-run this macro it was working fine excep one of the worksheets wasnt formatting properly....the macro on this sheet wasnt working for some reason.

Then this morning when i opened new files and tried the macros again it wouldnt work at all. And there is no reacord of them I did five of them and they are all gone! I saved them so why did they disappear! I thought that when you saved a macro as a PERSONAL file that you could open new files and re-run that macro again and again????

View 9 Replies View Related

Insert Rows Macro & Copy Formats

Sep 25, 2006

I need to find a way to insert a row (preferably at the bottom of an existing list) that mirrors the formatting of the row above.

I have had a stab at this (see attached example file) but my solution requires the user to select the correct activecell before it works correctly

View 9 Replies View Related

Macro To File Values & Formats, & Alter Protection

Dec 24, 2008

I have a sheet used to process sales orders - about 500 orders per month x ~1.5Mb each. I want to save them as smaller files.

The sheets to save are full of vlookups linked to external sheets and tabs, other formulas, and macros - most of which do not need saving when the sales order is filed. There are lots of merged cells too.

I must save:
1. Cell values
2. Cell formats incl merged cells, borders, colour, font etc.
3. The row and column sizes
4. Print set ups - print area, margins, page setup, header footer etc - (Everything needed to reprint to same as original)

I think I need a "File save as" style Macro which opens a dialogue box for the user to nominate the destination folder (& allows the user to browse for it), and a new file name.

The original file name is "Job Sheet - Omlaw.xls"
The tab/sheet to save is "Front Sheet"
(There are two other tabs - neither of which need saving.)

All cells to be changed to "locked".

The sheet must be password protected in Excel. User to enter it - or not.

The saved file should be "write protected" if possible?

View 7 Replies View Related

Macro To Keep Pivot Chart Report Formats When Filtering

Dec 17, 2011

I have a pivot chart, filtering data from a pivot table, the problem I've got stuck is that every time I filter the chart the colors and weights of the chart change to default values, not what I want, I've managed to every time I click on the pvt cht tab it updates with the colors and weights I want, but when filtering I don't know how to do.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved