Numeric Date Into Alphanumeric Date
Dec 28, 2012
One system I use provides an output of the previous day the system ran (which is the date I need) but provides it in a strange format. Today, for example, would appear in 4 different cells.
Day Month Century Year
28 12 20 12
I would like to take this output and change it to 28-Dec-12.
I tried bringing them together using the code:
[Code] ......
Hoping (but but pretty sure it wouldnt work) that Excel would read it as 12/28/12 and convert it to 28-Dec-12 but that did not happen.
Is there any way to do this without using VBA?
If not, is there an easy way to pull this info using a very simple macro?
View 3 Replies
ADVERTISEMENT
Jan 6, 2014
As the title says I was wanting to convert a date into an alphanumeric number.
After searching around for a long time the closest thread I could find was this one : [URL] ....
I would like to enter the date by dd/mm/yyyy and have it come out to an alphanumeric number.
The alphanumeric number for example would be C21501
Where "C" is the year starting from 2013. So in this case C would be 2015
Next two digits would be the week of that year. So 21 would be in the month of May
The following number would be the day of that week starting from Monday. So in this case 5 would be Friday.
The last two numbers would be the consecutive number for that day. For example two orders could be placed that same day so the first would be C21501 and the next would be C21502.
View 8 Replies
View Related
May 28, 2012
I have a column which contains User ID's, the user ID is created by our local IT and is composed of three letters and two numbers, my issue is that excel recognizes some of the ID's as dates i.e. Jul-25 when the user ID should be jul25. When I change the column to "Text" then the user ID become numeric... how can I change the date format "Jul-25" to Alphanumeric format as "jul25"?
View 5 Replies
View Related
Aug 19, 2012
How do we sort an alpha numeric date from recent to oldest.
from this:-
HGH010112
HGH231211
HGH020212
to this:-
HGH231211
HGH010112
HGH020212
View 2 Replies
View Related
Mar 15, 2013
The attached file Countifs issue.xlsx shows my attempt to construct a "CountIfs" formula that finds the one record out of ten that matches the following criteria:
a) Col A has 10 IDs. 3 of them are between 40 - 49, and another 2 are alphanumeric. This formula SHOULD identify these 5:
=COUNTIFS(A2:A11,">39",A2:A11,"<50",A2:A11,"4AB",A2:A11,"40C")
Doesn't work, It finds the 3 numerics but returns "0" when the two alpha-numerics are added.
b) Col B has a list of dates. 5 of the 10 are this month. This formula SHOULD identify them:
=COUNTIFS(B2:B11,(EOMONTH(NOW(),-1)+1),B2:B11,(EOMONTH(TODAY(),0)))
Doesn't work. It returns a value of "0".
c) Col C shows the Status for each record. 5 are "A" and 5 are "B". The following formula DOES find all the "B"s
=COUNTIF(C2:C11,"B")
But obviously I need the full formula to find the one record out of the ten that meets all the criteria.
in (a) and (b) and enable me to combine all three sections.
View 4 Replies
View Related
Feb 28, 2008
I've been googling for hours. I thought I was supposed to use some sumif statement, but I am having a hard time defining the criteria.
I have data that looks like this
STORE__ Date___ Beer___ Wine__ Deli
2 880101 45 43 24
2 880102 23 234 2343
.
.
.
3 880101 etc.etc.
etc. So i want to total monthly beer sales for each store. my sumif statement is looking like
sumif(B:B, ????, C:C)
I am trying to define the middle portion to give me January 88 if the values is between 880101 and 880131 but i can't figure out the right way to describe being betwen 880101 and 880131.
View 9 Replies
View Related
Jan 3, 2013
I have a string that with spaces in between.
example:
X3PP2 1200 U
I would like to extract the "1200". How can i do this in VBA?
View 4 Replies
View Related
Mar 2, 2010
A2 cell data = 11 digits alpha-numeric number.
* begin with either "0" or "1"
I want to the formula to look at the 6th and 6th digit (numeric, alpha-numeric, or Alpha) and compare it with the cells under "'PSCCR Queue - Manager" TAB from C4 thru C7.
Below formula is only looking at the first value when comparing.
=IF(A2="","",IF(ISNUMBER(MATCH(MID(A2,6,2),'PSCCR Queue - Manager'!$C$4:$C$7,0)),"ITS","Non ITS"))
View 9 Replies
View Related
Dec 31, 2007
I have created a macro that searches for an existing part in my worksheet and copies the found part to the row above. I would like to increment the value of cells B and H in the new copied row. Both cells contain an alphanumeric string that ends with a numeric value. For example cell B has a string like this "APL-DK0030" and cell H has a string like this "Dell Optiplex GX260 V09"....
View 9 Replies
View Related
Nov 10, 2009
I am wondering if you can sum the numeric parts of a range of cells containing alphanumeric values. Here is an example:
values to sum: 500, a20, ab30
expected results: 500 + 20 + 30 = 550.
In this project, the number of values to sum is large and I do not wish to use long formulas to extract the numeric part of each cell within the range. I am also forbidden to use up extra cells as an intermediate working out. Can you help?
View 10 Replies
View Related
Nov 17, 2009
I need to sum the numeric portions of any cell containing a certain letter within a row. I found a solution that works if all the cells within my row are either blank or contain a string with the "desired letter" lets say the letter is "a" so that we can compare it to ....
View 6 Replies
View Related
Jun 20, 2014
I want to strip only the leading set of numbers from a string. Trailing numbers should stay.
Logic rule would be:
1) Find first Alpha character
2) Remove all numbers prior to this position
Code:
Original string Desired string
42114DEP DEP
050114OP2OP2
0514OPOP
050214BKCKSBKCKS
05214CKCK
0502CKSCKS
050214CKS-3CKS-3
050214CKSACKSA
050214OPOP
50214PSCASH1PSCASH1
050214PSCKSPSCKS
0502SALES SALES
View 9 Replies
View Related
Aug 16, 2007
I have a cell with a value of, Text 1 and in the next field I want it to display Text 2, then text 3 and so on.
Is there any way of doing this? I guess I am looking for something like a1+1, just a shame it doesn't work.
View 9 Replies
View Related
Aug 5, 2014
I export data to Excel and now I have date like
05-08-14
06-08-14
70-08-14
If I change the Date format to AUG-14 and than pivot it than its coming in tree column which I need only 1 for whole month
View 4 Replies
View Related
May 14, 2009
I get data in csv format from an external source.
The problem is that it gives me dates in a lousy format of ddmmyy
For example 120706 or 050606.
Excel doesn't recognise them as dates and just displays them as numbers (120706 or 50606)
I want to see them as dates, so I select the column and change the format to date
BUT
excel sees my "numbers" as a julian date, and jumps me forward to the dates 24/06/2230 and 20/07/2038 respectively.
View 14 Replies
View Related
Jul 13, 2009
I need to be able to locate a date in a spreadsheet bassed uppon a user input date. I am looking for the week that contains the user's date. However it seems even if I initialize the value as Date, it still reads the user's input as a string.
Is there an easy way make sure the system automaticaly recognizes the user's input as a date, or is there a way to quicly convert the string to a date?
View 9 Replies
View Related
Sep 6, 2012
I have some dates in Excel in different formats and I need to convert them all to a uniform date format of MM/DD/YYYY.
The data is in this format:
82012
8152012
52012
5152012
The days of the month don't really matter. It's the month and year that I need in a date format.
View 2 Replies
View Related
Sep 27, 2007
I have been trying to create a userform that will generate a numerical list starting in a given cell using a starting number, an ending number, and an increment that the user will imput into a textbox. I have a userform set up, but I do not know how to do any coding. I have very little experience with excel.
View 2 Replies
View Related
Jan 2, 2007
I am using this formula to combine Excel Dates & Times which is in seperate cells.
day Month Year Time
=B13&"/"&C13&"/"&D13&F13
and trying to convert to Numerical Time for further calculations.
I have tried many Format variations but nothing seems to work.
It will show the correct Numeric Time if the Formula: =NOW() is used in Cell K12
But it won't show the same from the imported data that is Text format
in Cell I 12
The sample W. Book shows a better explanation of the problem.
The end result is to show how much time has lapsed in Cell M 14 by converting Time to Numerical and simply do the sums. Works otherwise but not in this instance.
The reason to trigger various macros according to pre-set Time Limits Etc.
View 9 Replies
View Related
Oct 3, 2011
I am finding many posting on this topic with unique numeric values and have not come across one in regards to a text value. The essence of the formula is looking through a list for unique email addresses and now I need to up it to a date range and eventually a store # range
I am using the following array formula to establish an overall count:
=SUM(IF(FREQUENCY(IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""), IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""))>0,1))
Ctrl+Shift+Enter
The date column is E:E.
The store number column is G:G
View 3 Replies
View Related
Aug 21, 2013
I have a column of dates formatted as:
20130201
The cell format in the column is General.
I need to change it so that the format looks like this:
02/01/2013
Is there a quick/easy way to do this in Excel 2010?
View 2 Replies
View Related
Nov 15, 2008
First and foremost I would like to congragulate you on this wondeful piece of code in the below link...
The query was to get a future date excluding Fridays and Holidays...
http://www.excelforum.com/excel-work...rkingdays.html
I have a similar query and therefore I pasted this link...
I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any..
Now Holidays would be official Public Holidays and
Leaves would be taken by the employee..
The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...
View 7 Replies
View Related
Apr 24, 2012
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
View 7 Replies
View Related
May 25, 2014
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies
View Related
Mar 9, 2014
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
View 4 Replies
View Related
Oct 11, 2008
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
View 2 Replies
View Related
Nov 19, 2009
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...
The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...
I use xl 2003.
View 9 Replies
View Related
Dec 20, 2007
Can anyone help me to get a validation to check that what is entered in a cell, lets say A1,
has to be number between 100000 and 899999 (this I already have) OR a value/text from I100000 to I899999. So it only acceots a vlaue betwwen 100000 and 899999 and it is also ok to have the letter I at the begining.
View 9 Replies
View Related
Mar 9, 2014
i have several styles to handle mentioned under different rows separately.
all the styles have their various raw materials inward date mentioned under different column of their respective rows from column Q to Y
now i need to 1. extract the latest date of any particular raw material which can be in any of column from Q to y columns ACCORDING TO DIFFERENT STYLES IN A Particular row automatically that is AC
View 1 Replies
View Related
Oct 13, 2009
i would like to use the formula "now()" to establish a date in a document. is it possible to lock this date to the initial date? (so when the document is created the date won't change the next day)
View 2 Replies
View Related
