Set Date Format For Specific Cell In VBA

Jan 26, 2012

What I have done is create a macro in Excel and then opened up the VBA editor to make a lot of changes to the initial macro. I am reformatting a whole lot of separate spreadsheets over many workbooks.

Here is the code that is generating the error (Run-Time error '438', Object doesn't support this property or method):

Code:
Range("E2").Select
Selection.Format = "long date"
With Selection
.Font.Size = 11
' .Format = LongDate
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

As you can see, I first tried the ".Format = LongDate" and did put LongDate in "" which did not work, then I tried the "Selection.Format = ..." after the "Range" statement, and that did not work either. So you will actually know, I am trying to set the format for the cell "E2" to a Long Date format.

View 7 Replies


ADVERTISEMENT

Format Cell To Always Insert Specific Thing When Specific Name Is Typed?

Apr 4, 2013

Is there a way to format cells to where - when you type in a certain person's first name, it will insert something entirely different?

This has seemed to have happened on a computer at work.

In Excel, when I type in "Martha" then tab, or otherwise leave that cell, Martha changes to "Cool"

If I type in anyone elses name - it stays that name. But if I type in "Martha" .. it changes to "Cool" with the bullet and the word Cool.

View 6 Replies View Related

Convert From A Specific Date Format

Jan 7, 2009

If A1 is 81209, how can I use functions in B1 to convert it into DEC/09/2008?

View 4 Replies View Related

Specific Date Format In Excel

Mar 11, 2014

Is there any way to format the date to DD/MM/YY, and user should be restricted in entering any other formats other than DDMMYY

View 3 Replies View Related

Convert A Date In Specific Format

Oct 19, 2009

I'm trying to convert a date in the following format: 19970121 to 1/21/1997 and am having trouble getting rid of the leading zero in the final version. I also need it to pick up the 2 digits in months that have them from 9 (Sept) through 12 (Dec), though, for use in a VLOOKUP function. Can this be done at all?

Here is the code I've been using so far that doesn't entirely work for what I need it to do:

=CONCATENATE(LEFT(RIGHT(B2,4),2),"/",RIGHT(B2,2),"/",LEFT(B2,4))

where B2 refers to the column the original date form (19970121) is in. I'm still stuck with the leading zero in this code.

View 9 Replies View Related

Format Date In Cell To Custom Format?

Feb 21, 2014

I have a column in excel which gets populated in the format "2/1/1980" when copied from a different source. I want a macro to run on this column and convert this in the below format. Required format: '02-01-80 (note the ' symbol before 02 which will keep it as text)

Existing ----> Required
2/1/1980 --> '02-01-80
2/1/1970 --> '02-01-70
2/1/1983 --> '02-01-83

I need this macro to run on two columns and replace them with the required format. I can do a find and replace either in the sheet or through hard coding the VBA, but as the date format keeps growing, I would need to do this via code and hence requesting the same.

View 4 Replies View Related

Format Specific Lines Within A Cell

Jul 7, 2009

I am trying to write a macro that given a selected range of cells goes in each cell and bolds the first 2 lines within the cell. An example of this would be if the cell contained:.............

View 2 Replies View Related

Prevent Automatic Date Format When Referencing Date Cell

Aug 1, 2008

I have been struggling with my Excel 07 Date format. Nearly every time I perform a calculation, enter in a value etc in a spreadsheet my answer is returned as a date. So for example if I enter in a cell: = 5 - 4. I get the answer "1-Jan-1900" instead of 1. I have to manually set the formats to general or number if I want the correct format. The setting seems to be the default one and occurs for all workbooks I open. What can I do to change this.

View 5 Replies View Related

Entering Specific Text Into Cell Based On Its Format

Apr 13, 2013

I'm trying to make a schedule for some of my employees (300 to be exact) and I was trying to work out a way of speeding things up a bit. I've made plenty of pretty complex equations for working with the task but the solution for what I would like to do now alludes me.

Here's the challenge: I have a sheet where I would like to insert employees within row 4 and then have their shifts shown visually in the column below that, and have the hours they work put into a merged cell (or something similar), like what can be seen in the blue cell under "Employee 1" (C6). What I would like to do is just have something like a VBA detect that a series of cells is merged (red cells) and insert the corresponding time, located within B5:B33, into that cell (with same format as the blue cell). Also, above the employee's name I could put a button to run something like a macro to run the program for the corresponding employee directly under that button.

Essentially I want to merge a series of cells, hit a button, and have excel locate and input the correct times within that merged cell.

View 1 Replies View Related

Conditional Number Format Based On The Value Of A Specific Cell

May 9, 2009

I have a large spreadsheet which includes many rows whose number format needs to change depending on the value of a specific cell.

So for example:

If cell B20 has value "A", cells Q20:AG20 should display as 1,234
If cell B20 has value "B", cells Q20:AG20 should display as 5.6%
If cell B20 has value "C", cells Q20:AG20 should display as 7.89
Etc...

As conditional formatting does not seem to include a number format option I've worked out that I need some sort of macro to change these settings for me, either automatically whenever the value of B20 changes or, more likely, when I manually choose to run the macro.

View 9 Replies View Related

Automatically Format Range Based On Specific Cell

Aug 19, 2008

I am looking for VB code that will format a range of cells from a number format to a percentage format based on a reference cell. The reference cell is a drop down created from a list validation.

For example: If the reference cell (cell A1) =1 then cell range B2:C4 would be a number format. If A1 = 2 then cell range B2:C4 would be a percentage format.

I have searched for hours trying to find a relevant thread; I know they are out there, maybe I just wasn't typing in the correct search words.

View 5 Replies View Related

When A Date Entered On A Specific Cell, Automatically Enter A Text In Other Cell

Jul 10, 2006

I have 6 Headings in excel named...

"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.

There are two projects.

Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.

My Specification follows...

1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.

View 3 Replies View Related

Insert Today Date In Specific Cell When Any Cell In Column Changes?

Nov 12, 2013

I would like to know how to use a VBA code to insert today's date into a specific cell (B9) when any cell in column B (B2:B8) has changed.

View 3 Replies View Related

Format Number To Text With Specific Format

Sep 14, 2007

I'll get straight to the point: How can I change this text format from 474556788 to 0-47455678-8 (dash added after 0 and before the last number). I need to apply this to around 5000 rows (can't do that manualy). I want it to be int his format:

474556788 to 047-455678-8

Tks Auto Merged Post;474556788 to 047-455678-8 (This is the wanted format)

View 10 Replies View Related

Date Format To New Cell

Nov 19, 2008

Dates:
------
November 1, 2008
1 novembre 2008

In the formula bar, these formated dates appear as 11/1/2008.

What I need to do is copy the formated dates and paste them into another column so that they show up as November 1, 2008 or 1 novembre 2008 and not as 11/1/2008.

Does anyone know of a non-macro formula or function that can do this? Is there a "paste as is" function?

View 9 Replies View Related

Replace Date With Value From Specific Cell

Feb 28, 2014

If a cell has a date in it (date payment received) I would like to replace it with the value of the payment received that is given in a different cell - how do I do it ?

View 2 Replies View Related

Colouring Cell If It Has A Specific Date

Feb 17, 2010

I have a column for each month where the dates are entered on rows. Is it possible I can color a cell if the date entered in a cell for that month is 15 days past the end of the month. Like, if in a January column I enter a date on one of the rows as Feb.20, then the cell fills with red colour. I tried doing it using conditional formatting, but don't know how to say 15 days past end of the month?

View 5 Replies View Related

Highlighting Cell When Enter Specific Date In Another Cell

Jul 4, 2013

I have a problem regarding highlighting a specific cell, when i entered a specific date in another cell

Let me explain

If there is a Name, joining date, termination date/resignation date in cell A1 A2 & A3 respectively.

If the name is John, Joining date = 01/01/2012, Resignation Date 06/06/13

I need a formula which highlights the cell A2 plus all those cells which have the same date when i entered a date (i.e 01/01/2012) in another cell.

View 2 Replies View Related

Cell Date Format Will Not Change?

Nov 23, 2006

I am facing a problem with number formats. I have downloaded some data from a database. In the data, there are some date columns in MM/DD/YYYY format. In a seperate workbook, I have copied that data as values.

Now when I am trying to change the formats of the dates to d-mmm-yy, they don't change. The formats are not not changing to any other format. I even tried changing the formats using VBA, but it did not work.

View 4 Replies View Related

Format Cell For Date And Time

Apr 13, 2009

I have a problem when i try to format a cell. i download data from a website and it gives me the date and time in the first column, ex. 1/1/2009 5:00:00 pm. when i format the cell to date, the time is erased and the date remains which is what i want, ex. 1/1/2009. however, when i click on that cell or any other in that column, the display above, or "insert function" line still show the contents of the cell with the date and the time, ex. 1/1/2009 5:00:00 pm. is there any way i can change that so the time is erased and only the date remains, ex. 1/1/2009 when i click on the cell?

View 9 Replies View Related

Cell Date Format Will Not Change

Nov 23, 2006

I am facing a problem with number formats. I have downloaded some data from a database. In the data, there are some date columns in MM/DD/YYYY format. In a seperate workbook, I have copied that data as values. Now when I am trying to change the formats of the dates to d-mmm-yy, they don't change. The formats are not not changing to any other format. I even tried changing the formats using VBA, but it did not work.

View 2 Replies View Related

Format Date In Cell As Text

Feb 2, 2008

I am pulling reports out of a non-windows based program. This program spits out the data directly into an Excel spreadsheet, with but a click of a button. I imagine that the designers of this program assumed that this would be useful, but it's actually proving to be quite irritating.

The Date format they have chosen for their program is NOT the date format that Excel uses, but it resembles it enough to cause serious problems. The original program tracks dates in a "dd/mm/yy" format, rather then Excel's "mm/dd/yy". To my VAST personal irritation Excel does NOT convert this into Text (which could be
manipulated by snipping it into the correct format) but instead converts it into it's own Date format, leaving me with a useless and incorrect serial number as the only data.

I cannot interact with the exporting process at all, and as the program exports DIRECTLY into Excel I cannot manipulate the data prior to import (which would be one relatively easy way of avoiding this problem). I am left trying to figure out a way to reconstruct an actual date out of this mess. Is there any way to convert a date serial number BACK into text?

View 2 Replies View Related

Date Countdown With Reference To Specific Cell?

Jun 25, 2014

I have this formula counting down days until my next fire drill:

[Code].....

However... Every time the drill is done I need to change the date in the formula for the countdown. That I can have it reference a cell instead?

My initial idea was a formula like this:

[Code] .....

(however this code doesn't actually work ...).

View 5 Replies View Related

Calendar That Inputs Date In A Specific Cell

Feb 8, 2009

I need the code to add the selected date to a specific cell lets say cell D8
at the moment it adds the date in any cell selected

View 4 Replies View Related

Pivot Table - Format Date In A Cell

Jun 7, 2013

I'm trying to copy the value of one column to another, column A has date and time in this format: 6/1/2013 6:35:00 AM and I'm trying to get ONLY the dates to another column so that when I do my pivot, I can sort it out by date. I tried =A1 and I can format the column to how I want it but the value of the cell still remains in date&time. Is there any formula I can use? or should I change anything in my pivot table?

View 4 Replies View Related

Format Date Cell With Week Number

Jan 8, 2009

just wondering if its possible to format a cell to display date and week number and if so how to go about it

eg

04/01/09 week 1

View 7 Replies View Related

Conditional Format One Cell Based On A Date?

Jan 18, 2013

I have two cells. both formatted to contain dates.

cell a1 will have a date in it. cell a2 will have a date calculated to be cell a1 + 42 days.

now, when cell a2 reaches a date that is greater than cell a1 + 42 i want cell a2 to be formatted as fill red.

so, i have tried a conditional format as =a1+42 >=TODAY() but it always highlights as red even if the value in cell a2 is less than cell a1+42.

View 5 Replies View Related

Automatically Format Date Cell As General

Jan 8, 2007

I have a cell that is constantly being changed from a date to a number. Excel automatically formats the cell as a date when a date is entered but if I re-enter a number it leaves the format as a date.

Is there anyway to automatically change the format back to general if a date is not entered.

I had this bit of code obviously it dosent work.

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E8:E46")) Is Nothing Then
If IsDate(Target.Value) Then
Target.NumberFormat = "d/mm/yy;@"
Else
Target.NumberFormat = "General"
End If
End If
Cancel = True

View 4 Replies View Related

Pass Date In Cell To Variable & Format

Sep 28, 2007

I'll get straight to the point:

where ( Date > 9/20/2007)

The above Date si used in a sql select statement where I'm hard coding the date (9/20/2007). This date is actually located in sheet1, cell E1. How can I get it from that cell and use it in my Select statement instead of entering the date manually every day in my code?

View 5 Replies View Related

How To Date Any Specific Cell When Another Cell Is Changed

Sep 28, 2011

I am trying to date stamp a cell when another cell has been changed from a drop down selection list. For instance: If cell A1 is changed, time stamp B1. I have tried both the following codes and they both work for 1 column.

"Private Sub Worksheet_Change(ByVal target As Range)
If target.column = 1 Then Cells(target.Row, 2) = Now()
End Sub"

[Code]....

The problem is that I want to time stamp cells in columns FHJLN, when the column to the left has been modified (EGIKM). So I basically want to repeat the time stamp code for multiple columns. I have tried messing around and can get not more than 2 columns to work at a time.

View 3 Replies View Related







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