Take the displayed date from a cell, in this case formatted as yyyymmdd, and simply display the date as plain text, not converted to the 1900 date system. For example, I have a date cell that reads 20080610, but everything I have tried returns 39609. But I want it to return 20080610 in plain text so that I can use RIGHT and LEFT functions to extract the year, month and day.
View 2 Replies
I am trying to have a cell in a diffrent work sheet display information from another sheet.
I entered ='Weapons Table'!A48 and it is displaying in text not what is actually in that cell.
on the cell above it I have entered ='Weapons Table'!A47 and it displays fine.
I've tried typing = then just clicking the cell, and it is still just displaying it as text. I've spent 3 days on this sheet and have only 1 error, that is driving me crazy.
I need some vba code that will enable the users of my spreadsheet to populate a range of active cells with their Windows Username,time and date.
Quite simply I have approx 30 people accessing one spreadsheet and each person needs to update some info on the page.. So what I want is a time stamp so i know at what time they finished their updates and will also confirm their usernames...
I am currently having a very painful problem with dates.
Currently I assign a value which happens to be a date to a string.
I have in my 'Daily' sheet I9 which needs a formula to look at the date in C9 and the scan range 'Weeks' F6:AN41 and find the cell with the matching date and then whatever text is in Row 2 of the column the date was in will be returned.
View 2 Replies View RelatedI am looking to write a formula that will return the row or column number of the cell that contains a particular text within the string. I know it can be done with script, but need it as a formula. Also, to add another level of difficulty, I'd like it to be a traditional formula, no special keystroke to activate. It will be used within a much larger formula.Ex. If G5 has the string "CURRENT DATE" in it, and I look for "DATE", the formula would return 7 for the column or 5 for the row.
View 4 Replies View RelatedI've data from A1 till G1. Now I will hide column B & D I just want to have total of displayed columns that must ignore values written in column B & D
View 1 Replies View RelatedI am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
I want to sum cell A1 and result will be displayed in B1
AB1ValueResult29292.50*10+8952*15+8550*10
I have the following data on a spreadsheet created by a formula:
A1: 1.1234
A2. 2.234
A3: 3.34
I have the data formatted to only have two decimal places so it looks like this:
A1: 1.12
A2: 2.23
A3: 3.34
I'm using the following formula in cell B1 to display the values of those cells in a single cell in order A1,A2,A3:
=A1 & "," & A2 & "," & A3
However when the values of A1, A2 and A3 appear in the in cell B1 they appear as: 1.1234,2.234,3.34
Is there a way I can edit the formula or format something so the values in B1 appear as with only two decimal places? i.e 1.12,2.23,3.34
I want to be able to record the highest value displayed in a cell (b2) in another cell (c2). Cell b2 is a cell that has continuously updating numbers from a live feed, which are updated approximately every second.
--I'm not sure if I need to have a log created based on the changing numbers. If so, I wouldn't anticipate the length of time to determine the highest value would be more than 7 hours.
--Is there a function/filter that would be able to report the highest number recorded?
I inserted the Date & Time picker Control into my worksheet. I have it linked to a cell. When I change the date, the new date goes into the cell, but it does not appear to be a "date" . The date formulas that use that cell do not evaluate properly. It looks different than when I type a date directly in to the cell (it is left justified). I tried formatting the cell as a date and the format property of the control is set to shortDate.
View 2 Replies View RelatedI'm typing the formula straight into the cell and funny thing happens: the cell displays the formula, but not the result...
SO now I can see =A2+B2 and no values.
Is it possible to display only one character when using drop down menu? For example, if the drop down choices are A=...., B=...., etc., is it possible to only display the A or B in the cell after you've made your selection?
The purpose of this spreadsheet is to obtain data that will be entered into an application. So it would be very useful if it could be printed on one page - and the columns only show A, B, C, etc. Also, as it is now, the spreadsheet is difficult to work on because there is so much visible text.
his is of Jaafarian proportions. This is in relation to an earlier post on dragging and dropping. That is all worked out thanks to help from board members. For the sake of your own curiosity, this project will tile worksheet windows (actually specific ranges from other worksheets and other workbooks), borderless, within a single userform and allow dragging and dropping between ranges that reside in different workbooks. That is all worked out as well. The only roadblock I am running into right now is that I cannot edit directly in the cell when the worksheet is displayed in the userform. Probably has something to do with placing a worksheet in a userform to begin with.
CTRL-R to show the form or run maco, "ShowForm". If you double click a cell and attempt to go into edit mode, it will appear to be locked up. It's not, just hit enter to escape the cell. The problem is the userform is a child window of the application. Hence, focus can pass to the parent window or another child (the formula bar in particular) which is exactly the behaviour expected. How do I get around this?
How could I create a calendar?
I have the month displayed in cell A1.
Cells A2 - G2 display M T W T F S S
How could I have the cells below this display the correct date, on the correct day, depending on the month displayed in cell A1?
I have a bunch of images which I want to be able to display in different cells depending on certain values or conditions. Say for example you have a drop down box with a list of fruits. When you pick a fruit, it displays its picture. Is this possible? If all of the pictures exist somewhere on the sheet, can you reference the pictures to have them appear in a cell rather than having images always "floating" over the sheet?
View 3 Replies View RelatedI need to analyse more than 4500 product of my company. I have data in more then 20 separate excel worksheets, I need a function or a vba code to return the text against my product names.
I my main file i have the names of the products, sales and purchase data and analysis. In other files I have the categories list against those products. In the main file the products are according to brand and categories have different products from different brands grouped together. I want a function something similar to sumif but for text.
Example
What I need
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
A-123 bca loreal skin care anti acne anto acne soap
Main file
Art no. Product Name Brand Group Category Subcategory Q1 sales Q2 sales Q3 sales
y-123 abc lux 120 110 105
A-123 file with categories against respective products.xlsxmain file what i have.xlsxmain file.xlsxwhat i need.xlsx
Category files
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
I have two (2) different values in the same column one value is text (INV) the other is a time date stamp 05/18/2012 10:48:32. The text i want to return in a seperate column for these two is if it is INV then the result is "PENDING" if it is a date 05/18/2012 etc. then the result would be "PAID" example:
Payment Status
05/08/2012 10:30:12
INV
05/17/2012 08:27:37
INV
and so on...................
I am trying to find a way to write a Macro that will auto color multiple cells based on what data is displayed in one cell. The cell I want to reference is a vlookup cell.
Basically this is a part label. And depending what part is selected from the list my vlookup will display its position on a vehicle(i.e.. FR, FL, RR, RL, Etc..). So if vlookup comes back with FR I want the various cells on the label to be orange, etc..
ALSO: if there is a way to embed it so it does this automatically (rather than run the macro each time).
The message box in the Standard module procedure returns 00:00:06.
Standard Module
Code:
Sub New33()
Dim clsCampaign As CCampaign
Set clsCampaign = New CCampaign
clsCampaign.MailCode = "AWSXBETC"
[Code]....
Code:
Option Explicit
Public MailCode As String
Public LandDate As Date
Public MailDate As Date
Public FirstPanel As Date
Public FinalPanel As Date
how to write an excel workbook function that will return last friday's date?
For example: If Today is 2/12/2008, it will return 2/8/2008 as the date
I have a list of dates in column A which are in order but non consecutive. I am creating a new table with all consecutive dates. I need a formula to check whether a date is in column A and then give out a number in the corresponding column to the right. I am able to use Vlookup for this but if the date is not present in the first coumn it returns an N/A response. Is there a way of returning zero if the date is not present?
View 5 Replies View RelatedI am trying to set up a query to search a date range within a table in one sheet based upon a more narrow date range in a second sheet AND return a simple count, median currency value or value based upon a ratio (see attachment).
Note: the Sale Price, List Price and Supply values will be based upon the filtered date range (each quarter) and return a value based upon that range. The Sale Price and List Price also need to be a median within each quarter value so I can chart the data.
Help.pdf
Here is what I need to return:
Num Sales - simple count from each quarter
Num Listings - simple count from each quarter
Sales Price - median value within each queried date range
List Price - median value within each queried date range
Supply - numeric value based upon absorption rate, supply = listings each quarter / absorption rate (absorption rate = sales each quarter / months each quarter)
i have a column in a spreadsheet that stores a date and another column that i want to store a date category. I want the "date category" column to return the date from the "date" column as: eg Jan 09 (if the date is eg 24/01/09)
View 2 Replies View RelatedI have 2 tables where I need to pull data from one table into the other.
Table 1 - pull data here
A
Site
B
Begin Date
C
End Date
Rate
[Code]...
For example: site 77 in table 1, I need to pull whatever rate is shown for site 77 in table 2 for the date range specified on table 1. So the correct answer for the first row site 77 in column E would be .02. Notice there is a problem...mainly the dates do not match exactly. I just need the rate the applies for the majority of the time.
Another example. For table 1, site 174 from 2/11/11 to 2/22/12, the correct answer in column E (pulled from table 2) would be .02, even though Column D shows .00.
I have a spreadsheet with 2 worksheets within it. These are simply called sheet 1 and sheet 2.
On sheet 2 I have cell A1 containing =(TODAY())
Also on Sheet 2 I have cells A28 to A39 containing Month names and cells B28 to B39 containing values. Like this
January 1000.00
February 1000.00
March 2500.00
April 1250.00
May 1750.00
June 2200.00
etc
How can I return the sum of these values into Sheet 1 B18 based on the date. That is to say that I want to add up the sum of Jan, Feb, Mar, Apr, May up to and including callcualtaions made on 31st May but as soon as the date goes past 1st Jun I'd want to include June as well and so on.
I'm trying to streamline my spreadsheet a bit. Sometime last year I managed to use the formula found (on the attached file) in cell K4 to assign a numerical value to a lettered grade. However, it's been a year or so since I thought about it and so I've completely forgotten how to edit it to make it more appropriate for this spreadsheet!
Essentially what I'm trying to do is a workaround for what I've already done in columns E and F: I'm trying to show the difference between two grades (letters) as a numerical value. Is there a way to do this, or is the VLOOKUP work around I've used in columns E and F going to be the simplest way?
here is my text strong:
PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
101915744. MMU - DO NOT BREAKAP:
I want to return text starting with ALLOC but stop at and not include AP.
| item1, item2, item4 |
Elsewhere I have a set of points for these like this:
| item1 | 4 |
| item2 | 2 |
| item3 | 3 |
| item4 | 1 |
(the |'s represent cell borders, if it isn't obvious)
What I want is a formula that can grab the text, then produce a total score. So, for my example, the result would be 7, totalling the scores for item1, item2, and item4.
I know that the score lookup is trivial (just a VLOOKUP), but I can't figure out how to do the scan for the data from a single cell. I want the items to remain in a single cell if possible.
