I 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 RepliesI'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
How 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.
I have not learned the custom number format feature. If someone could give me the format for the cell to display:
4/23 Wed
Fellow 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.
hi 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
I'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.
I 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.
i 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...
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.
View 2 Replies View RelatedI need to custom sort a column. I have 3 different types of data in the column. First - multiple dates, Second - "TBA", and Third - "ASAP". What I need is when the column is sorted the "ASAP" rows will be first, the dates (sorted) will be next and finally the "TBA"s. I have been trying to use a custom list.
View 2 Replies View RelatedI have a date in H34, 21/03/2009.
In I34 I have the same date again, except formatted as MMMM YYYY so its showing "March 2009".
In G10, I want the cell to read: Number at end of March 2009, with the month and year being taken from the date range. To be honest, I can get the year okay using the Year() function but cant get the month as cant convert it back from a numeric.
I am needing to create a sequential serial number using a format yyyy-nnn, where yyyy is the current year and nnn is a sequential number (2007-001). I've tried using both a formula and custom formats but cannot get the cell contents to display as desired. I've used TODAY()&"-"&"001", which gives a valid result of 39100-001, but this not what I need. I've tried many other combinations in the custom option for formatting the cell.
View 5 Replies View RelatedI am trying to get a custom date format that looks like:
30MAY07
I can get 30May07 with ddmmmyy but can't seem to find how to get the 3 letter month to display in caps.
Excel 2002 SP3 on WinXP
I would like a simple way to make a single column whose rows are sequenced Mon, Wed, Mon, Wed, ... and have the correct dates. For example:
Mon 2/11
Wed 2/13
Mon 2/18
Wed 2/20
Mon 2/25
etc.
Cell A1 could have Mon 2/11, Cell A2 would have Wed 2/13, and so on. (20 to 50 rows)
The ordinary Fill Series when applied to Mon Wed will continue with Fri Sun Tue Thu and so on, or it will return Mon Wed Mon Wed Mon ... with the wrong dates. Also, the solution should be applicable for a Tue, Thu(TTh); or Mon, Wed, Fri(MWF); or Mon, Tue, Wed, Thu (MTWTh) series.
I 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.
I have a lot of data in the following format: 20080531T085930. Formatting the cells with yyyymmddThhmmss doesn't seem to be working. What's the correct way to handle this?
View 3 Replies View RelatedFor making quotes at work, I'm looking to have the quotes named automatically. The format of naming our quotes is the date (in this format) then quote name: yymmddNAME (Example for today: 090720EXCEL)
I'm using the =NOW() function for the date (Cell A1), then doing a custom format to turn the format into yymmdd then in a different cell I'm putting in the "NAME" (Cell A2) and what I want to do is combine the 2 cells (Cell A3) by doing: =A1 & A2
but when i do this, the format of the date gets messed up.
I'm trying to use the Date Time Picker control as a time picker and only want hours and minutes to show. Can this be done with its custom format property (Example)? The usual number formatting string doesn't seem to work.
View 6 Replies View RelatedI created a formula to stop me from having to do an If statement in the case of an error in one of the variables...I'm pretty sure it work fine in all cases I used yesterday, but today when I use the formula to calculate eCatch (e8+e9,e9) the formula result is #name, even when then e8 and e9 are numbers...the vba is embedded in a module in the excel sheet used, so I'm not sure why its coming up with the error.
View 4 Replies View RelatedI have some custom formula =CFORMULA(...).
I put it in some cell, for example A1.
When this formula executes I have a value, but if some condition in the formula not true I don't want any value. I want the value in A1 to remain the same.
For example:
=IF(C1>5;M1;A1)
If I write like this I have cycling. It is not good. I want just to return the value that already was in A1. I change the =CFORMULA with =IF just to explain the problem.
Actually I have my own formula.
I have a set of data being output by a reporting application as XLS data. For each day of business the report is run, I have a set of 100 rows of data. In each of the these rows of data there is a date field and a locationID field.
What I need to do is add a calculated value on each row that specifies the day of the week (MON, TUES, WED, Etc.) and which occurrence of that days of the week it is for the data set. The enumeration of which occurrence of the day of the week (ie. MON-1, MON-1, MON-3) is determined not by how many dates fall on Monday for the data set, but how many Mondays fall between the earliest and latest dates for that location in the data set.
Example:
The data set contains sales data for location XX from 4/6/09 (A Monday) to 4/22/09 (A Wednesday).
The data set contains sales data for location YY from 1/18/10 (A Monday) to 2/9/10 (A Tuesday).
Example:
Location XX's data's earliest date is 4/6/09, it's latest date is 4/22/09
From 4/6/09 to 4/22/09 there occurs:
3 Mondays
3 Tuesdays
3 Wednesday
2 Thursdays
2 Fridays
2 Saturdays
2 Sundays
Location YY's data's earliest date is 1/18/10 it's latest date is 2/9/10
From 1/18/10 to 2/9/10 there occurs:
4 Mondays
4 Tuesdays
3 Wednesday
3 Thursdays
3 Fridays
3 Saturdays
3 Sundays
For all data for location XX, I need to set the new calculated columns to:
Where Date = 4/6/09 & Location = XX, DOW# column = MON-1
Where Date = 4/13/09 & Location = XX, DOW# column = MON-2
Where Date = 4/20/09 & Location = XX, DOW# column = MON-3
Repeat for all dates between min-max date for that location
For all data for location YY, I need to set the new calculated columns to:
Where Date = 1/18/10 & Location = XX, DOW# column = MON-1
Where Date = 1/21/10 & Location = XX, DOW# column = THU-1
Where Date = 1/25/10 & Location = XX, DOW# column = MON-2
Where Date = 1/24/10 & Location = XX, DOW# column = THU-2
Where Date = 2/1/10 & Location = XX, DOW# column = MON-3
Where Date = 2/4/10 & Location = XX, DOW# column = THU-3
Where Date = 2/8/10 & Location = XX, DOW# column = MON-3
Repeat for all dates between min-max date for that location.
A typical data run will have data for 4 or more locations spanning 30-60 days, so figuring out a formula that I can drop onto a column of the exported data so i can run pivot table analysis would be great.
Round to Nearest Half Based On Defined Range of Decimal thread.
So here is what happens in the first half of the equation....I round numbers to the nearest half based on a specific range of decimals in the original number. For example if the number in A1 is:
28.0 to 28.399 then rounds DOWN to 28
28.4 to 28.799 then rounds to 28.5
28.8 to 28.99 then rounds UP to 29
For this I am using shg's equation:
[Code] ........
This is all I needed for most of my numbers.....but one part goes one step further.....
NOW THE SECOND HALF......Once the first half of the equation rounds the number above, I need the second half of the equation to then do the following. If the resulting number from the first part of equation above is:
1 through 3 = its own size (1 is 1, 1.5 is 1.5, 2 is 2, 2.5 is 2.5, 3 is 3)
3.5 to 7 = 3
7.5 and above = 4
Resulting number from last part of the formula multiply by 2
So this will end up being only 1, 1.5, 2, 2.5, 3, or 4 multipled by 2.
I am pretty sure this second part would be an IF statement (or maybe there is a more efficient way), but I am still learning the ins and outs of that....but I don't know how to use the first formula and add the needed second part into all one formula.
The test takers are shown three pictures. Under each picture is a number. So for example, let's say there is a plane, a car and a train presented for question #1. Underneath those pictures are the numbers 1, 2, and 3 respectively. Those numbers represent a tendency to a certain occupational field. After 55 questions, we can make a pretty good recommendation of where we think they should look for a job (without going into the specifics of the test).
There are 11 occupational fields. In excel, I make those fields A through K. In field L1 through L55 I put in their answers.
Let's say that in all 55 questions, for example, answering 1, 3, and 5 lead to vocational choice K, I want it to tally it up for me automatically. I don't want to have to count those by hand.
I just want to put in the patients answers, and have excel do the work. I tried using the "countif" function, but it doesn't look like you can add multiple conditions.
I need something that will be like this: add 1 each time (as in a tally) to the cell I choose, for the answers that I specify. So, ultimately, it will be tallying up the answers based on which categories that I correspond them to into the respective cells.
So to be redundant... Let's say that answering 3, 7, and 9 to questions 1, 2, and 3 pertain to cell B. After I put those in, I want cell B to have the number 3 in it.
I'm working on creating a custom formula that loops through each row of data and performs a range of calculations when a common number appears in a field (payroll number).
The formula is being applied to 2 sets of data, both are similar however 1 has a few more fields.
When I use the custom formula in the 2nd dataset I keep getting a Circular Reference.
I have attached a stripped down version of the whole spreadsheet, which just relates to the part causing the problems.
On the sheet 'Teaching (Yr1)', the calculation works fine.
On the sheet 'Support (Yr1)', the calculation doesn't and Excel prompts with a Circular Reference.
I've tried using the Circular Reference toolbar to trace the predecessors, however it doesn't highlight what they are. All the inputs are blue, and none of them perform any calculations on the cell/column with the formula in it (unless I'm being exceedingly blind!)
Excel gives the following message ...
Originally Posted by Excel
Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you.
I've spent many hours (probably 10+!) debugging the formula trying to ascertain why it's happening and I'm stumped! I've run through all the named ranges, I've changed the formula and still no success.
What I have noticed though, that for some reason the same formula is 'called' multiple times, in fact 3 to be precise.
When I enable Tools -> Options -> Calculation -> Iteration, the formula calculates, however whenever the spreadsheet does an full calculation, it reverts back to #VALUE!
I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)
The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.
For example, I want I37 on most sheets to have the same formula. So I have the following:
I am creating a custom autonumber in excel. The autonumber will be based on the value of another cell's value. So for eg, in cell A1 will look at B1, if B1 has the number 1, A1 will take B1 and add the autonumber to it, eg a, b, c.
If within, b5 the number is 2. So if the number in the corresponding row in b changes, the autonumber must restart.
I've decided to go with a-z for the autonumber as i realized for .1, .2, .3 is limited to 9 values in the list bec. 1.10 may be read as 1.1
I've tried a few options such as creating a named range, however the problem is getting the autonumber to restart.
Below is what i want to achieve via excel formula:
Column F
Column F
1.a
1
[Code] .........
I've created a custom Function. I would like for there to be a Helper when a user is entering the Function arguments.
For example if I go into a cell and type in =VLOOKUP(
A popup with appear that shows...VLOOKUP(Lookup_Value,Table_Array,col_index_num,[range_lookup]).
Is it possible for me to build something like this in my Function?
is there a way to turn the following formula into a function ?
=IF( COUNTA(A1:A7)=SUMPRODUCT((A1:A7<>" ")/COUNTIF(A1:A7,A1:A7&"")),"No Duplicates","Duplicates")
I just have no idea where to start ?
How do I set up a custom validation formula to prevent duplicate enties?
For example I I've already enter the song name 19 and Paul Hardcastle (BandName), how do I set it up where I can not enter that combination again on a row?
I was wanting to use a custom CountIF function but I could not get it to work.
A
B
Song
BandName
19
Paul Hardcastle
[Code] ....