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?
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.
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"?
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:
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.
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.
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"....
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?
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 ....
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.
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?
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.
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.
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
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?
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.
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)
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:
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.
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...
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.
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
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)
