When I take data from another file it comes in as text in the form of 20120705.
Is it possible to convert this in Excel to a date format of 05/07/2012? Have to do it without macros as the file is e-mailed on
and will get deleted/and or quarantined by our spam filter if it has macros within.
I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
But Excel does not like the formula at all.
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 my excel worksheet (excel 2010) I have one cell that changes every day (number). I want this number to open my htm document and replace the same number in a string in the htm and save/close this.
An example:
My htm document is located at C:/ and named XX.htm
The number I want from excel is in cell A1 in sheet1, and the worksheet is located in D:/ named yy.xlsx
And the text(number) I want to replace is in the following string in the htm document, in this string it is 72, next day it can be 30:
src="Bilder/72.png"
I 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 have run a report from one of our databases and it gives me the date in the following format :
Apr 26, 1983 12:00 AM
When i sort it does it alphabetically - i think the cell is actaully text (general) format - i can't seem to change it to any of the inbuilt date foramts - i would like to sort my spreadhseet by date !
I am trying to put together a report tracking sheet, ie I have different test that take a different length of time to return to the office and I want to be able to see when a test should be back from the lab,ie:
A blood sample takes 1 day, a urine sample takes 3 days.
I need a formula that basically says that if the data in cell A1 = Blood, (and I have the test date in cell A2) then the data in cell A3 should says A1=Blood, A3=A2+1 to give me the test due date
I found the following formula in one of the other posts;
=IF(E5="BLOOD",G5+1,IF(E5="URINE",G5+3,0))
The problem that this will only work with two tests and I have more than two tests, I have fourteen and each takes a different length of time to process.
I have a number of comment (all column D) that have lots of data in each one.
The data will appear like this (each new entry (new entry denoted by a date))
01-May 12:58:05 Liam
02-May 16:18:27 Josh
07-May 11:51:26 Bob
02-May 01:13:34 Terry
What i want to know is, can i change the colour of the text in the comment, if its todays date. So in this case 07-May 11:51:26 Bob goes red (its the 7th here for me).
User of Excel in Office 2007. However, I for statistics about how my Bitcoin Device's works and decrypts Bitcoins per day for trends and statistics, and so on
Now while I was away so had the power gone, and for almost 24 hours so stood all still, I would now like to outline in red the date / dates where I have zero or very low running time for specific date, but I can only change Fragen for all dates, not individual dates.
The stack's not exist in the data value is equal to zero (null) where by I want to be able to get the date in red color, and possibly also in bold. see print screen below.
[URL] .....
I am trying to find a way in Excel 2007 to convert the date from US format to UK format, I tried to change it by using [right click=>Format cell=>Select Date=>Select the first option in Type:*14-03-01=>OK], but I doesn't work , the attached file are presenting the date in US format.
View 10 Replies View RelatedI have a very basic understanding of excel, I mean BASIC, for my work I have multiple dates for vehicles to go in for inspections. I would like the cells to change colours when within 30 days, 10 days and 5 days. I'm on a work excel 2003.
View 6 Replies View RelatedAt work I have a register/log. Its just a printout of a standard format in excel sheet. I print a month of sheets at a time and write the day and date on it by hand at the top. Is there some formula in excel by which it prints the dates as well in a progressive manner. Eg. If today is 29th May 2014 and today I print 30 copies of the register/log. The first copy has todays date on it and the the next one had tomorrows date....and the 30th copy automatically has the end of April 2014 printed on it.
I've found this code but cannot get it to work.
My workbook is called Shift Log, the worksheet is called v2 and I'm using Excel from MS Office Professional Plus 2010 (32bit)
#Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
Dim sDate, i
retryDate:
sDate = InputBox("Enter the starting date, or click 'OK'" & _
" for the current date", "Start Date")
[Code] .........
I'm relatively new to Excel and I'm currently making a basic spreadsheet for my personal income/spending.
How would I make the "Earned this Month" and "Spent this Month" tabs in the top update on a month to month basis automatically?
For example, in the month of may, it would display may's values, june would be june's etc. etc.
(Excel 2010 w/ Windows 7)
I have a SS of a WW1 casualty list and one of the columns is Dates of Birth but the data has been entered in three different formats eg 21/2/1898 or 21.2.1898 or 21 Feb 1898. I have tried to Format Cells and change the date format to the slash but after highlighting a number of fields and right clicking they will not re format. Though i use Excel quite a bit i am a general user and dont understand it in depth.
article using =DATEVALUE(
am using Office 2007.
In Excel 2007 I know that if you have a cell containing text that you can change the color of parts of that text like This is the text in my cell and this is red text
But I have a cell with a formula that adds text to the end based on a conditional. I can't see any way to change the text color in this situation. I basically want this formula:
=D_Notes & TEXT(CHAR(10),) & IF(Terms = "Budgetary Estimate Only", "Quote is budgetary only and is subject to change pending final design.","")
Ii have a UserForm with a ComboBox in it. the selections in the box are
0
5
10
15
20
25
30
I have 7 shapes in Worksheets("Interface") and renamed each shape caution1 through caution7
What i need is when i click on continue in my userform each shape will change according to the selection.
if 5 is selected all caution shapes will read, Caution flag is out 5 minute break
if 10 is selected all caution shapes will read, Caution flag is out 10 minute break
if 15 is selected all caution shapes will read, Caution flag is out 15 minute break
and so on
excel 2007 btw
Example attached : Copy of CAB.xlsx
View 2 Replies View RelatedBasically I first want to be able define around 100 variables such as:
Dog = 1
Cat = 2
Mouse = 3
And so on. The reason being my final data needs to be the numbers for it's purpose but because there are so many I can't possibly remember them all to just type out 23 | 44 | 76 | 2 etc. What I would like to then happen is that whenever I type out the words in a cell it's automatically replaced with the number ID.
So typing in
Dog
Mouse
Mouse
Cat
Dog
would automatically result in
1
3
3
2
1
In those cells when I hit enter or at the end in one go. Is this at all possible in Excel 2010?
As part of a program I'm writing in VBA (for Excel 2010), I have a textbox in a user form used as an interface to write a formula in cells in Excel.The resulting value of a formula from a cell is loaded up into the textbox. It would be shown in the textbox like "See 1.2 and 1.3" where the formula in the cell is
[Code].....
This is just used as an example but the principle is there. It is worth noting that I’m writing this for very inexperienced Excel users but I need them to be able to edit the string part of the formula without breaking the formula.
However where I’m struggling is to pick up a selection change event inside a text box already selected. I need to be able to check if the textbox.SelStart is within an address value or within the string in the textbox.
The event Enter won’t work if the user is already editing the textbox (i.e. typing stuff) and then clicking or using the keyboard arrow to move the cursor somewhere else inside the textbox. I don’t think the event Change is the solution either as it would mean that the user would have already typed something and as a result the formula may already be broken.
I have had a good look around and I didn’t find an event for a selection change inside a textbox. Does it exists and/or is there a way that would have the same result?
I have two columns of data and cannot get the column to change the format to display as currenty or accounting, with a currency £ sign and comma separator...I select it all and go to Format cells to do the changes, but it does nothing, at all!
View 2 Replies View RelatedWhen I combine 3 reports (which we get out of a system) into 1 big file, the date format remains the same (mm/dd/yyyy and right alligned). Same happens for most of my colleagues.
When 1 particular colleague goes and combine these reports, I've noticed that some of the dates are showing as text? (dd/mm/yyyy and left alligned).
My formulas is
="Start Date: " & VLOOKUP('Project Information'!$C$4,Sheet1!A285:T285,19,0) & "
End Date: " & VLOOKUP('Project Information'!$C$4,Sheet1!A285:T285,20,0)
The results I want are Start Date: MMM-DD-YYYY End Date: MMM-DD-YYYY
What I am getting is: Start Date: 41537.7083333333 End Date: 41537.7083333333
So excel is give me the numeric values of the date instead of the formatted date. I tried to format the cell as date, but as I expected that didn't work because the cell also has text in it.
I have a date range that looks like this (British date style, not US):
edit: I posted an image but it can't be seen for some reason:
Date Range
2010 11/10 17/10
2010 11/10 17/10
2010 11/10 17/10
2010 11/10 17/10
Thus the first cell show 11 October 2010 to 17 October 2010 I want to return just the first day (the week commencing date):
edit: I posted an image but it can't be seen for some reason:
Week Commencing
11/10/2010
11/10/2010
11/10/2010
11/10/2010
I have written some VBA to the best of my ability but it seems clumsy using the FORMAT function. Is there a better of doing this with VBA. I can't seem to find a function in VBA which is the equivalent of the formula function DATE.
Code:
Sub ConvertTextToBritStyleDate()
' dd/mm/yyyy (not mm/dd/yyyy)
' Select the cells containing the dates (not the header)
[Code]...
i have the text as string data in column A and it is just a text not date format. i want to convert this using formula text function to get result shown in column B and C. is this doable?
Excel 2010ABC1DateRevised DateRevised Date 2Fri 4 Feb 2011Friday, February 04, 20112/4/20113Fri 5 Oct 20074Fri 28 Apr 20065Fri 30 Sep 20056Fri 23 May 20087Fri 3 Feb 20068Fri 30 Sep 20119Fri 11 May 200710Tue 1 Jan 201311Fri 13 Aug 201012Tue 25 Jun 201313Fri 8 Jun 201214Fri 25 Mar 201115Fri 12 Feb 201016Fri 5 Sep 200817Fri 6 Nov 200918Fri 6 Mar 200919Fri 21 Aug 200920Fri 6 Jan 201221Fri 5 Jan 200722Fri 15 Jan 201023Fri 12 Jun 200924Fri 7 Jul 200625Fri 21 Oct 201126Fri 31 Oct 2008Sheet4
I'm creating my task file in excel 2007. In this file there is column for task description, Intimation date, start date, Set completion date, completion date and remarks. I want in intimation date cell, cell contain the date which is 5 days before the start date and the cell/text(date) blink until the start date come. if the task completed in the defined completion date, "complete in time" is written automatically in remarks column, and if completion date is after the set completion date for the task "delay in completion" written automatically in remarks column.
Sr. No.
Intimation date
Task Description
Start Date
Set Completion Date
Completion Date
Remarks
I have a question about conversion of a text format into date format.
2 computers, the first windows vista, the second Windows 8, both Excel 2007.
The purpose is to calculate the difference between 2 dates with hour:minutes:seconds, like for example:
18/12/2012 19:20:00 and 18/12/2012 19:23:03.
So in this example the result should be 00:03:03.
Both dates are formatted with 'personalized' : dd/mm/yyyy hh/mm.
In the first computer everything works like it should, but in the second it is apparently impossible to transform the first of those date h:m:s which is originally in a text format into a date format. I tried all the methods which i found to transform a text date format into a date , like multiplication with 1, Datevalue and others, but nothing seems to work, the result is always "#Value".
Is there a formula which places the date In A5 whenever text is entered in A2 - Excel 2013
View 9 Replies View RelatedI want to use a formula, in another cell, to convert "Friday, 30 May 2014, 3:47:16 PM" to a value. I am using Excel 2003.
View 2 Replies View RelatedI have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.
I work for a UK charity and have a list of funders in an Excel 2007 spreadsheet.
One of the columns refers to the date on which a new application for funding can be made to that particular funder.
In many cases new applications for funding can't be made for 1 or more years since the last application - sometimes as many as 5 years later. How to get a cell to refer to the date that it contains.
For example, say I have in cell A1 "The Acme Funding Organisation" and in cell B1 (i.e. the "Reapply when?" column) a date of 01/04/2013 (British date format, i.e. 1 April 2013) then what I want Excel to do is to look at the date in cell B1 and if that date has been reached to highlight the cell red. That way I'll know that the reapply date has been reached & that a new application can be made.