Show A Custom Date Formate
Feb 8, 2007hi i have a formula in a module that show a date as such
"ddd d" and this shows up as say "fri 6"
how can i get it to show up "f 6" or maybe "fr 6" and not have to use the whole three letters
:o
ADVERTISEMENT
Enter Custom Date Range To Show Data
Dec 10, 2008I took an Excel course in college but am a bit rusty. I have a customer database that I created and I would like to see certain things after entering a date range that I specify. I uploaded a small excel sheet to show my database and what I'm looking to do. Basically I would like to enter a date range and see these answers:
Total Orders in that date range
Total unique orders in that date range
List of customers (name included) who ordered more than once in that date range
The only columns of data I need to use are: firstname, lastname and orderdate.
1800's Date Formate
Aug 13, 2009working on a sheet I have one col that is formated for dates (yyyy-mm-dd) this is fine if you enter a date from the 1900's or 2000's but enter a date from the 1800's and it's a no go!
example:
Enter 14 jul 1941 and it formats 1941-07-14 no problem
Enter 14 jul 1841 and you get nothing just 14 jul 1841 no formating at all. And this is the same for all the date formats.
Replace Date Formate
Jul 7, 2008I'm using Excel 2000 and I have downloaded a report from another system. The dates come in for example in the format 10.06.2008. Using the replace functionality I can change this to 10/06/2008. However, when I do this using VBA
Range("L49:L300").Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
it works on most but some of the dates get switched to 06/10/2008.
Force TextBox To Be Date FormatE
Apr 30, 2008I'd like to import some dates into some textboxes on a userform.
I'm also going to let the user edit these, however... what i'd like is for the user to be forced to:
1) enter a date in the format dd/mm/yy
2) Enter a valid date (eg, not feb 30th)
Number/Text Format: Custom Formate "0000-0000-0"
Dec 4, 2008I have this data 0123-4567-8 but in the formula bar it only shows as 12345678. When I looked at the format it has as Custom 0000-0000-0 and it would be correct. The formula that I used to extract the numbers without the dash is =LEFT(A3,4)&MID(A3,6,4)&RIGHT(A3,1) the result is 12345678 So when I reference this number to 012345678 I get the wrong answer.
View 3 Replies View RelatedSheet Custom Properties Show Up
Dec 9, 2006See custom document properties for a give workbook by using File, Properties but where do sheet custom properties show up.
View 9 Replies View RelatedCustom Functions Show #VALUE! After Macro Has Run
Aug 20, 2008Recently I found this forum through a Google search along with the perfect solution to a problem I was having finding and replacing text based on a table of replacement values. Here is the thread:
Multiple substitute or replace text using a table
I incorporated the SuperSub function that steveorg developed as a result of that thread into my worksheet by creating a new module and pasting the following code into it:
Function SuperSub(OriginalText As String, rngOldText As Range)
Dim cel As Range
Dim strOldText As String, strNewText As String
' loop through list of old_text used in substitute
For Each cel In rngOldText.Cells
strOldText = cel.Value
strNewText = cel.Offset(0, 1).Value
OriginalText = Application.WorksheetFunction.Substitute(OriginalText, strOldText, strNewText)
Next cel
SuperSub = OriginalText
End Function
It works like a charm as a formula, but I also have a macro in the same workbook that, as soon as it selects the sheet with the formula, calls the SuperSub function and starts executing the code as a macro (even though there is no call to this routine in the macro). The macro ran just fine before incorporating the function into my worksheet.
The formula looks like this: =TRIM((supersub( UPPER(E2),rngSubst)))
If cell E2 contains the string "101 North Main Street, Apartment 5", it would return "101 N MAIN ST APT 5"
Here is the macro:
Sub Import()
'
Sheets("Shoebuy FTP").Select
Range("A2:R200").ClearContents 'This is where it jumps to Function SuperSub(OriginalText As String, rngOldText As Range)
The function runs for every occurrence of the formula in the worksheet, and when it is finished, all of the cells containing that formula show a value of "#VALUE!". I should mention that it does not change the formula at all, but I then have to highlight each cell that contains the formula, press <F2> to edit, then <ENTER> to get it to display the correct results again.
It is probably some stupid little thing that I didn't do when I created the function, but I can't figure it out. I have written numerous macros, but this is the first time I have ever created a custom function.
Show Custom View Upon Exit
Sep 26, 2008I am trying to set a custom view when exiting, but I keep getting runtime error 424 saying an object is required. I am attempting to unprotect the sheets, check the value of an option button, set the custom view based on that value, and then protect the sheets. Please keep in mind that the user may or may not be on the sheet containing the option button when exiting. Here is the code I am having trouble with.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call UnprotectSheets 'Unlock to set option button value
With Worksheets("Customer_Info")
If btnExistCust.Value = True Then
ActiveWorkbook.CustomViews("Sales_Exist").Show
Else
ActiveWorkbook.CustomViews("Sales_New").Show
End If
End With
Call ProtectSheets 'Call the sub before closing
End Sub
Display/Show Custom Error Message
Aug 31, 2006I have a userform with a combobox (cmbSearch) , a textbox (textbox1) and a listbox (listbox1)....and a commandbutton (cmbFind). This userform is used for searching records in my worksheet....where user selects a "Search By" category from the combobox and then enters relevent text search criteria in the textbox....and all the search results are shown in the listbox. This part works fine untill someone tries to search a "non-existent" record. e.g. a customer name thats not in the database and it gives an error. I would incorporate in my code to have a Message box pop up on these type of searches saying "textbox1.value Not Found!" insetad of error message.
Private Sub cmbFind_Click()
Set c = Range("a65536").End(xlUp).Offset(1, 0)
'write userform entries to database
Dim DataSH As Worksheet
Set DataSH = Sheets("ComplaintData")
With DataSH
.Range("L1").Value = cmbSearch.Value
.Range("L2").Value = TextBox1.Text
.Range("N1"). CurrentRegion.Clear
.Range("A1").CurrentRegion. AdvancedFilter Action:=xlFilterCopy, criteriarange:=.Range("L1:L2"), copytorange:=.Range("N1")
End With
lstSearch.RowSource = vbNullString
lstSearch.RowSource = "outdata"
End Sub
Custom Number Format :: To Contain (and Show) Both An Interger And Text
Nov 29, 2009I would like to format a row of cells. I want each cell in the row to contain (and show) both an interger and text. The interger will range in value from one to ~10,000. The text will always be two characters long...but these characters will change from cell to cell.
Elsewhere in the spreadsheet I will have a cell (w/ a formula) that counts the values of the intergers in the row, but ignores the text in each cell.
a procedure for format ing the cells in the row?
Custom Number Format To Show Difference In Two Dates
May 11, 2006I'm trying to get an excel custom number format to show the difference in two dates as 0 years and 0 months...for e.g.
Cell a1 = commencement date = 1/1/05
cell a2 = expiration date = 12/31/10
Then cell a3 = Lease Term = 6 years 0 months.
Show Custom Error Message When No Data To Paste
Aug 27, 2007Below is a macro I recorded to help copy 'Paste Special - values only' data between two spreadsheets that have different colour and conditional formatting in cells.
However, if this is run and there is no data to paste, it throws up the End and Debug error.
Any chance that someone knows that if there is no data sitting on the Clipboard, then a msg box would appear saying "Please select the data you require to be copied" in the message box.
Sub data_input()
ActiveSheet. Unprotect
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B5:AG4804").Select
Selection.sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B5").Select
ActiveSheet.Protect
End Sub
Show UserForm Before Printing For Custom View Choice
Apr 9, 2008When I click the excel printing icon, I want a userform to pop up in which I make the choice for a certain customview that I build. This customview must then be printout. Note that I want to use the standard Excel print icon, not a custommade one in the worksheet. I figured out some code, but it doesn't work. For instance my if structure returns "false" even if I did click that commandbutton, see the code below.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Static PrintRequest As Boolean
formMyview.Show
If formMyview.CmdCancel= True Then
MsgBox "printrequest canceled"
Unload Me
cancel=True
Exit Sub
End If
If PrintRequest = True Then
Exit Sub
End If
If formMyview.CmdOk = True Then
For Each Myoption In frameViewoptions.Controls
If Myoption.Value = True Then..........................
Show/Hide Specific Columns For Custom Views
Jun 12, 2008I have a spreadsheet with four columns of data for every month (i.e. January has a Prior Year, Budget, Outlook, and Actual column). I have been trying to come up with a macro or form that will allow me to customize which columns I would like to see and hide the rest. For example, if I chose to see the Budget and Actual columns, it would hide the Prior Year and Outlook columns for each month.
View 2 Replies View RelatedCustom Formatting - Show Numbers On Excel Spreadsheet In Thousands
Jun 14, 2013I want to show numbers on my excel spreadhseet in thousands. However, the challenge is I want to show negtive numbers in red within brackets. For instance, I have a number -104,784,089. I want to display it as (104,784) in red color. Similarly, I want to show 577,090 as 577. Is there a customer formatting I can set up in excel that do that?
View 2 Replies View RelatedShow Custom Error Message If Opening Workbook Macro Code Fails
May 17, 2008I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?
Private Sub btnOK_Click()
Application. ScreenUpdating = False
Dim LCSfile As String
LCSfile = frmSelectFile.Listbox1.Value
On Error Goto ErrHandler
Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV"
ErrHandler:
MsgBox ("File is not quantitated. Please select another file.")
Application.ScreenUpdating = True
End Sub
Retain Text Formate
Jun 11, 2009I have a list that looks like this:
1. Here I have som formatted text.
2. Here too.
10. And here.
110. As well as here.
The content of each line is in a single cell.
How do I go about automatically chopping off the number, the dot and the space before the text, but retaining all formattings?
Conditional Formate VBA Based On Other Cells
Jul 17, 2007I am trying to convert the following macro to a Worksheet (section change) format, but cannot figure out how to have the cells B87:M150 change based upon the text (red, green, white, amber, na) in O87:o150.
Sub ordinate()
Dim r As Range
For Each r In Range("o87:o150")
If r.Value = "RED" Then
r.EntireRow.Interior.ColorIndex = 3
ElseIf r.Value = "AMBER" Then
r.EntireRow.Interior.ColorIndex = 44
ElseIf r.Value = "WHITE" Then
r.EntireRow.Interior.ColorIndex = 2
ElseIf r.Value = "GREEN" Then
r.EntireRow.Interior.ColorIndex = 4
ElseIf r.Value = "NONE" Then
r.EntireRow.Interior.ColorIndex = 2
ElseIf r.Value = "NA" Then
r.EntireRow.Interior.ColorIndex = 15
End If
Next
Range("N86:IV132").Interior.ColorIndex = 2
Range("A86:A132").Interior.ColorIndex = 2
End Sub
Conditional Formate: Fill Is Light Blue And Bottom Line Solid
Feb 14, 2010Using 2003, I'm trying to do the following, data starting in A3, and ending in L3:-
- If A3 is blank, cells have no format
- If A3 has a value, with A4 blank, fill is light blue and bottom line solid, top line dotted
- If A3 has a value, and A4 does too, fill is light blue, top and bottom line dotted
- If H3, which is a date field, is greater than today font is black, if less than today, font is red
DR & CR Match: Formate To The Modern Ledger Type Where There Are No Left And Right But Rather Laid Out In 1 Page
Apr 28, 2006I have DR and CR entries which are laid out. in the traditional T format (under the Ledger tab). I would like to have it formatted to the modern ledger type where there are no left and right but rather laid out in 1 page. The required layout is shown in the Ledger attachment
View 2 Replies View RelatedCreate Calculator To Show Start And End Date Based On Specific Date Provided
Jan 12, 2014I am trying to create a calculator which will show start date and end date based on the specific date provided.
EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).
Show Week Number Of Today Date From Starting Date
Jun 27, 2014is it possible to display the week number of todays date (today()) from a physically entered start date (which would obviously be week one), the start date would be november 4th 2013.
View 3 Replies View RelatedDate Custom Format
Nov 22, 2006How do you make custom date format to a cell in such a way that it appears e.g. "Jan-06"?
I tried custom format "Mmm-yy" but it still resulted to "jan-06".
I want the "J" here to be in the upper case.
Custom Date Format
Apr 23, 2008I have not learned the custom number format feature. If someone could give me the format for the cell to display:
4/23 Wed
Custom Date Formula
May 22, 2006I have entered a date like May 21 in a cell. I want to enter a formula that will take that date and automatically add 7 days to it (after I create and run a macro of course). I just have no idea what formula to use to continually change the date from the 21st to the 28th to June 4th etc. each time I run the macro on a copied excel worksheet.
View 9 Replies View RelatedCustom Date Column
Mar 18, 2007Fellow Forum Members,
The formula below generates the dates for every Tuesday and Friday for every week of the year by doing a Fill Down operation.
=A145+IF(WEEKDAY(A145)=3,3,4)
My objective right now is to generate the dates for every Wednesday and Saturday for every week of the year by doing a fill down operation. What I can't figure out is how to I modify this formula to give me dates only for Wednesday and Saturday. The Help Excel provides for this function is not too clear. I would appreciate it if anyone can explain what is the logic being used for the key numbers "=3,3,4)" ? These numbers is what controls the days of the week that show up with dates, but the logic being used doesn't make sense to me.
Custom Date Picker Control
Mar 29, 2007I'm building a userform excel application and am using the MS date/time picker control. I've run into the familiar "computer doesn't have OCX" problem and want to avoid using a custom OCX.
I was thinking of just making my own control instead. Has anyone made one in VBA? I found some VB examples which I could port over, but they use a lot of api calls. For reliability, I'd like to keep this as simple as possible though.
Custom Date Format: For 2 Dates
Jun 15, 2007I want to display 2 dates in one cell and have them custom formatted.
Eg. Display in one cell: 26 Jun - 27 Jun
With text typed: 26-6 - 27-6
(Or something along the lines of)
I know that dd mmm will display one date. But I cannot get 2 dates formatted. I tried dd mmm; dd mmm" - "dd mmm, which didn't work.
Custom Date Format To Be Retained In .csv Also
Mar 14, 2007i have the required date as part of a string, which i need to pull out and then format it as "yyyy-mm-dd hh:mm:ss" and put it in another file which will be saved as .csv
Here is the code i am trying to modify without much success so far
Sub dateFormat()
Dim dtMydate As Date
Dim sMydate As String
Dim strWhen As String
strWhen = "As on 13-MAR-2007 14:45:59 Hours IST" ' the starting string
'strWhen = Cells. Find("As on")
'take out the date portion from strWhen
strWhen = Mid(strWhen, InStr(strWhen, "-") - 2, (InStr(strWhen, "Hours") - 1) - (InStr(strWhen, "-") - 2))
dtMydate = CDate(strWhen)
Debug.Print dtMydate 'returned value is 13/03/2007 2:45:59 PM
dtMydate = Format(dtMydate, "yyyy-mm-dd hh:mm:ss")
Debug.Print dtMydate 'returned value is again 13/03/2007 2:45:59 PM...