Date Isn't Formatted With VBA?
Aug 6, 2012
I have a strange issue. I have column "B" has date texts in it ( don't know if it means value) like 21.09.2012. The cells are in text format.
VB:
Sub ReFormatCell()
Range("B:B").Select
Selection.NumberFormat = "dd.mm.yyyy ddd"
With Range("B:B")
.Formula = .Value
End With
I tried ".Value = .Value" or ".Formula = .Value" or Copy paste or offset...... Non of the codes didn't work to make date format as I desired :/
The order is something like:
1. Format column "B" as text.
2. Offset text value 21.09.2012 to B.
3. Reformat cell as date
4......?!?!
What should I change to display "21.09.2012" as "21.09.2012 Wed" in B:B range?
View 9 Replies
ADVERTISEMENT
Nov 16, 2006
this code works fine on my desktop but on 2 of work's pc's it produces a error at the msgbox line
Private Sub Workbook_open()
'http://www.ozgrid.com/forum/showthread.php?p=306542&posted=1#post306542
Dim dDate As Date
dDate = Sheet16. Range("a1").Value
MsgBox "Today is the " & Format(dDate,"dd/mm/yy"), vbOKOnly
End Sub
i have changed the security settings in macro's and one of them was using office 2000, not sure about the 2nd one but i will check next time iam at the office and grab the error codes.
View 2 Replies
View Related
Oct 29, 2006
how can i get a cells date into a message box string? i have been trying to get this to work
Dim Date As String
Date = Sheet16.Range("a1").Value
MsgBox "Today is the & Date ", vbOKOnly
View 3 Replies
View Related
May 3, 2007
I have a cell E1 that is actually a vlookup and returns a date. I had to custom format the cell so the value returned looks like "June 2007" instead of "39827". I am running a macro and I need a messagebox to tell the user that they are creating an email for June 2007 (which is really the variable cell E1). Problem is the message gives the date as '6/4/2007' instead of 'June 2007'. I tried using the text() function to format the cell as "mmmm yyyy" but I get a type mismatch error. Below is the code I wrote. The first way shows successful but the date as numeric. The second gives me the type mismatch error: First way:
a = Range("e1").Value
Msg3 = "You are about to create an email for " & a & ". Are you sure?" 'Define msg
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
Title3 = "Create new file" ' Define title.
Response3 = MsgBox(Msg3, Style, Title3)
If Response3 = vbYes Then ' User chose Yes.
'create email
Second way (mismatch error): a = text(Range("e1").Value, "mmmm yyyy") Msg3 = "You are about to create an email for " & a & ". Are you sure?" ' Define message.
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
Title3 = "Create new file" ' Define title.
View 2 Replies
View Related
Oct 17, 2013
I'm working with a big dataset and the dates come out in this way:
94WAO7t.png
EDIT: Example of book included
View 10 Replies
View Related
Jan 13, 2010
I have the following formula in cell B1 to return the Sheet Name as the cell value ...
View 6 Replies
View Related
Oct 9, 2013
I have a column that contains dates from our system that displays in "mmddyyyy" format (i.e., 10121999 or 8121998). How do I convert this to a 10/12/1999, 8/12/1998 formatted column?
View 1 Replies
View Related
Mar 19, 2008
I am trying to get a single cell to display the following:
Last Updated: 3/18/2008 15:08 (GMT+2)
Entering =NOW() in a cell displays the date and time as required.
But entering ="Last Updated: "&NOW()&" (GMT+2)" displays the date and time as a serial number. Formatting the cell to Date does not change the serial number to date and time format.
The only way I have found to get the desired result is to use =NOW() in another cell (F13), format that cell to general to get the date/time serial number, then use ="Last Updated: "&TEXT(F13,"m/d/yyyy h:mm ")&" (GMT+2)" in the required destination cell.
As I said this works, but it strikes me as an inefficient method. Is there a formula I can enter or formatting I can apply to get the desired result without using an addition cell?
The result needs to be in a single cell. Splitting text and date/time into 3 adjacent cells will not work with my worksheet setup.
View 9 Replies
View Related
Aug 4, 2009
I need to calculate the difference in Years, Months and Days between:
Date 1 = TODAY()
Date 2 = 4 years after a date in cell A1, which will always be earlier than today's date
(A bit of backround - I have certain risk management procedures that have a lifespan of 4 years. I want to calculate the time between now and 4 years after the date the procedure was completed, essentially to see how long before they have to be redone).
So far I have:
=DATEDIF(A1+4,TODAY(),"y")&"y "&DATEDIF(A1,TODAY(),"ym")&"m "&DATEDIF(A1,TODAY(),"md")&"d"
But that returns #NUM!.
Removing the +4 obviously just calculated the difference between the date in A1 and today, but I need the date in A1 PLUS 4 years and today.
I have also tried:
=(DATE(YEAR(A1)+4,MONTH(A1),DAY(A1))-TODAY())/365.25
which works in theory, however:
a) no consideration for leap years
b) does not return nY, nM, nD - only the decimal.
However I would be happy to use this method if I could convert it to Years Months Days.
View 11 Replies
View Related
Aug 19, 2012
How to create the formula for dates formatted with periods.
Dates are as follows:
01.07.12 - should return as 1-Jul
01.08.12 - should return as 1-Aug
02.08.12 - should return as 2-Aug
View 4 Replies
View Related
Nov 11, 2009
I'm trying to link two different budgets into a third spreadsheet. The final step I'm attempting to accomplish is have an if-and statement reference just the month from a date-formatted cell. One of my departments tracks their budget on a daily basis, i.e.
11/2/2009, 11/3/2009, etc. rather than just 11/2009. I want the if-and statement to return the budget from all days in a given month so i can apply the formula to the whole year, broken down monthly too. I saw a question similar to this one but didn't see any responses so I'm not sure if it can be done.
View 6 Replies
View Related
Feb 13, 2007
I'm working with a line of code I can't seem to get right. I've got this string that will put breaks in, but I've realized it would make my life easier if the code, along with inserting a break, then inserted a formatted gray divider, it would make it easier. Here's what I have:
Sub BreakSections()
'This macro breaks the new data on sheets into their individual groups, inserting a blank line_
'which will need to be filled with a gray dividing line
Dim i As Long, j As Long
Application. ScreenUpdating = False
With Sheets("Contract Manufacturers")
. Cells.UnMerge
i = .UsedRange.Rows.Count
End With
For j = i To 2 Step -1
If Not IsEmpty(Cells(j, 5)) And Cells(j + 1, 5) <> Cells(j, 5) Then Rows(j + 1).Insert
Next
Application.ScreenUpdating = True
End Sub
I've tried then to put this code in after the Insert:
Range(ActiveCell, Range("AJ" & ActiveCell.Row)).Select
and then the formatting code.....
But I end up getting a whole mess of gray rows, displacing a lot of data. Is it possible to do these things together, or I should write another macro that just looks for the blank spots? It seems like it would be easier to do it all at once.
View 4 Replies
View Related
Jan 9, 2009
I have Excel 2008 for Mac (vers 12.0). On a basic spreadsheet, the first column is for date which I have formatted so I can type in 15/8/9 and it appears as 15-Aug-09.
However, each time I open Excel after having Quit it, I have to reformat the cells in this column again as they revert to converting my date input with something like 39,123.
My other columns are formatted to currency and are always okay.
View 10 Replies
View Related
Mar 2, 2009
I'm trying to copy about 10,000 barcode over to a new spreadsheet, the prob is that when I copy them over they appear as XXXX+12 and then when I try and use them it doesn't like it. I can solve it if I format the column to text - and then double click it. This is ok for a hundred or so but fingers start to hurt after that!
View 14 Replies
View Related
Nov 3, 2009
I have a cell that calculates an etch time, eg 41.88. The cell is formatted to give me whole numbers and quarters only so that the operators input is made easier, in this case 42.
When I use a macro to copy this to another worksheet, although it appears as 42, the actual cell data is 41.88. Anyway I can get the actual cell data to be 42?
View 2 Replies
View Related
Mar 30, 2012
I have written below code
Sheet1.Cells(1,2)=Shett2.Cells(2,1)
and my sheet1 cell has a value which is bolded and after running above code the sheet2 cell is displaying unbolded value
how to copy the format as well
View 4 Replies
View Related
Dec 8, 2012
Is there an excel formula to count the formatted cells for each row?
Here an example;
Sheet2
A
B
C
D
E
F
G
H
I
J
K
1
EXAMPLE
COUNT
2
01 05 31 34
10 18 21 28
23 26 33 36
06 11 37 39
15 19 25 30
16 17 35 38
02 03 08 09
04 12 27 29
13 14 24 40
07 20 22 32
3
[Code] .........
View 4 Replies
View Related
Sep 6, 2013
I have a project where if the user enters 'R' into cell H17 then cells B19:K19 are to be filled with black color. These cells are divided into three fields: B19:E19, F19:H19, and I19:K19.
When I select these three fields and use the conditional formatting tool the only cells that are black are B19:E19. Even though the rule explicitly shows that cells B19:K19 are to be affected. In order to have all three fields black I have to make three rules (one for each field).
The same behavior happens when I use the conditional formatting tool and select the cells from within the tool's dialog.
How can I get all three of the fields formatted with one rule?
View 2 Replies
View Related
Oct 24, 2006
I have a cell that I've formatted to dd/mmm/yy, It used to work, but now it doesn't. When I type in 1/2/03, the formatted cell shows 0-Jan-00, The formula bar shows =1/1/2. I seem to have a formula in the cell, but I can't get rid of it. I know it's most probably somethinmg I've done , but I don't know what. I've tried everything I know. I can't seem to re format the cell , and I can't delete the formatting in it. I'm stuffed.
View 9 Replies
View Related
Jan 10, 2007
Is it possible to sum cells that contain numbers converted to text? I used TEXT function to convert them because I needed to format them (to display three decimal digits, if number < than 0.05, three digits otherwise). But now SUM Formula doesn't work.
View 4 Replies
View Related
Apr 3, 2014
I have two columns with entries consisting of numbers. I'm trying to match between the columns but some names dont match because of the formatting.
Example
065 matches 065 (I have that green triangle on the cell that indicated "The number in this cell is formatted as text")
However 120 doesn't match with 120 (only one of them have that green triangle)
I tried to format all the cells as text at it seems the match only works when the green triangle is available. The green triangle seems to only show its self when I double click on the cell. I have thousands of entries so that doesn't work. I also tried to use the "Text to Columns" but it coverts entries like 002 into 2 which I dont want.
View 7 Replies
View Related
Dec 30, 2008
I want to be able to "count" apples and oranges. Is there a way to record a particular text in a text formatted cell and count it. e.g. 10 cells, 3 say "apples", three say "oranges", 4 say "plums". I want excel to keep track of the three types of fruit when I change them and give me a running number of each.
View 2 Replies
View Related
Jan 6, 2009
I want to find strikethrough text and replace it with blanks. In my sheet there are cells that contain both strikethrough and normal text. I tried using the 'Find and replace' tool, specifying the format. I've attached a picture with the settings from the Replace window.
The problem is that Excel finds the cells that contain strikethrough text, but replaces with blank ALL the cell content. I would like to replace only the strikethrough text from the cell and leave the normal text as it is!
View 4 Replies
View Related
Nov 13, 2009
Column A has a custom format of 000. Therefore the numbers in that column always have leading zeroes if they're less than 3 digits long.
I want to copy these leading zeroes to another column, but I DON'T want to have to custom format the new column. I just want the values copied over with any leading zeroes, if applicable.
When I "Paste Special" and select "Values", numbers like "1" that show up as "001" in Column A will show up as "1" again in the new column. How can I get the actual value "001" to be all that's in the new column?
If it's easier for you, pretend I need to copy over these custom formatted values in to another program which does not retain any custom formatting.
View 14 Replies
View Related
Dec 15, 2009
I have been asked by my manager to make new shelf labels for all our stationery products for the VAT change in the new year. I'm sure I can get excel to help me make the task quicker but i'm just having trouble with pasting the formulas into the label template that I have to use, I was wondering if anyone could tell me why and if it is possible to do with the way I set the document out. Attached is what I have so far.
if there is a simpler way of doing this. The data sheet is the item description and the old price. The labels sheet i need to be able to print off in that format so i can cut it up and use the labels on our shelves. The formula for the VAT change is on the labels sheet, it works on the old price on the data sheet. I want to be able to quickly apply the formulas and format of the labels to more cells in the sheet so i can print off multiple pages of labels.
I have tried just copying the current formatted cells (A2 to C29) which I have seperatley typed the formulas into (takes ages) and pasting them beneath that. It doesnt continue the formula though. Instead of the next label cells formula carrying on and being Data!A43 it become Data!A29. Perhaps what I want to do is not even possible!
View 2 Replies
View Related
Apr 14, 2009
I need columns A, B, and C to have borders all the way around each cell in the row. Then, columns D through H need to be merged, with one border around the merged cell. The row height needs to be size 16 font, which I've been solving by putting in one letter that size, colored white so it doesn't show up when I print.
Basically, I have a spreadsheet of a bunch of tools, and I need to insert a row below each entry for the mechanics to sign their initials for three stages of the repair process, and also have the larger, merged cell for notes.
View 7 Replies
View Related
Dec 14, 2005
ANYONE KNOW, OTHER THAN CUT AND PASTE, A METHOD OF "flip flopping" names in a list, from last, first to first last? (maybe "telling"the list to separate into 2 columns the words separated by the comma, last in one column and first in another). I have a list of 360 names and not looking forward to cut and paste this list.
View 13 Replies
View Related
Jul 8, 2009
I have a whole number in cell C2, but i am formatted it in Cell C3 to always have leading zeros combined with the number in Cell C2, where the result will be 8 digits long. example, my number in cell C2 = 572 but i need it formatted to be 00000572, but if the number was 19 it needs to be formatted to be 00000019 and so on.
View 2 Replies
View Related
Apr 18, 2011
I have roughly 100 HTML files, each containing a single table of formatted data (created with the SAS statistical software package).
I realize that I can open an HTML file with Excel, do what I need (e.g., copy, format, save as an Excel file), but I would like to try to automate the process, if at all possible, even if it's simply open the HTML file and save it as Excel. That would save me quite a few clicks it seems.
Ideally I could pass a directory name to Excel (via VBA) and have a series of "operations" (i.e., save as an Excel file) performed on each HTML file in the directory, ultimately resulting in a corresponding collection of "new" files (in my case, Excel files).
If this was very fancy, I would love to take each HTML file and copy the one table in each, and paste into a separate worksheet in a new Excel file (so 100 HTML files would result in one Excel file with 100 worksheets).
I did try to generate a macro while using the Import External Data "tool", but didn't have luck with that (although I am able to get the tool to work, just not within a macro environment).
View 9 Replies
View Related
Jan 20, 2012
I have data which needs to be formatted with a thousand separator as below.
400.00
1,000.00
250,000.00
How can I do this without it displaying as below
,400.00
I do not want it reflected in the case of hundreds.
View 3 Replies
View Related
