How do you extract day/time/month/year information

Hello,

I came across this issue,  where I have a exported excel file.  With the 
information in a field "Friday 02:15:00 March 2009".  I need these 
information extracted.  I only require Month/Year.  I need to end up with a 
field with only "March 2009".   Is there a formula for this?


Thanks for your help.  
0
Utf
2/3/2010 8:40:12 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

7 Replies
1133 Views

Similar Articles

[PageSpeed] 58

Try this:
=MID(A1,FIND(" ",A1,FIND(":",A1,FIND(":",A1)+1))+1,20)
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"SV" <SV@discussions.microsoft.com> wrote in message 
news:22C67718-C96D-4B46-A1BB-565BB92D3541@microsoft.com...
> Hello,
>
> I came across this issue,  where I have a exported excel file.  With the
> information in a field "Friday 02:15:00 March 2009".  I need these
> information extracted.  I only require Month/Year.  I need to end up with 
> a
> field with only "March 2009".   Is there a formula for this?
>
>
> Thanks for your help. 

0
Bernard
2/3/2010 8:55:30 PM
If really at date, Just format it as "mmm yyyy"or use a formula in the next 
column
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"SV" <SV@discussions.microsoft.com> wrote in message 
news:22C67718-C96D-4B46-A1BB-565BB92D3541@microsoft.com...
> Hello,
>
> I came across this issue,  where I have a exported excel file.  With the
> information in a field "Friday 02:15:00 March 2009".  I need these
> information extracted.  I only require Month/Year.  I need to end up with 
> a
> field with only "March 2009".   Is there a formula for this?
>
>
> Thanks for your help. 

0
Don
2/3/2010 8:57:24 PM
SV wrote:
> Hello,
> 
> I came across this issue,  where I have a exported excel file.  With the 
> information in a field "Friday 02:15:00 March 2009".  I need these 
> information extracted.  I only require Month/Year.  I need to end up with a 
> field with only "March 2009".   Is there a formula for this?
> 
> 
> Thanks for your help.  


=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200))
0
Glenn
2/3/2010 9:14:28 PM
=MID(A1,FIND(":",A1)+7,99)


"SV" wrote:

> Hello,
> 
> I came across this issue,  where I have a exported excel file.  With the 
> information in a field "Friday 02:15:00 March 2009".  I need these 
> information extracted.  I only require Month/Year.  I need to end up with a 
> field with only "March 2009".   Is there a formula for this?
> 
> 
> Thanks for your help.  
0
Utf
2/3/2010 10:20:10 PM
Thanks, for your help.  It worked.  :)

"Bernard Liengme" wrote:

> Try this:
> =MID(A1,FIND(" ",A1,FIND(":",A1,FIND(":",A1)+1))+1,20)
> best wishes
> -- 
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> 
> 
> "SV" <SV@discussions.microsoft.com> wrote in message 
> news:22C67718-C96D-4B46-A1BB-565BB92D3541@microsoft.com...
> > Hello,
> >
> > I came across this issue,  where I have a exported excel file.  With the
> > information in a field "Friday 02:15:00 March 2009".  I need these
> > information extracted.  I only require Month/Year.  I need to end up with 
> > a
> > field with only "March 2009".   Is there a formula for this?
> >
> >
> > Thanks for your help. 
> 
> .
> 
0
Utf
2/4/2010 2:08:01 PM
Thanks, Glenn.  Your formula worked also.


SV



"Glenn" wrote:

> SV wrote:
> > Hello,
> > 
> > I came across this issue,  where I have a exported excel file.  With the 
> > information in a field "Friday 02:15:00 March 2009".  I need these 
> > information extracted.  I only require Month/Year.  I need to end up with a 
> > field with only "March 2009".   Is there a formula for this?
> > 
> > 
> > Thanks for your help.  
> 
> 
> =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200))
> .
> 
0
Utf
2/4/2010 2:09:03 PM
Thanks, for your help.  Your formula worked :)

"Teethless mama" wrote:

> =MID(A1,FIND(":",A1)+7,99)
> 
> 
> "SV" wrote:
> 
> > Hello,
> > 
> > I came across this issue,  where I have a exported excel file.  With the 
> > information in a field "Friday 02:15:00 March 2009".  I need these 
> > information extracted.  I only require Month/Year.  I need to end up with a 
> > field with only "March 2009".   Is there a formula for this?
> > 
> > 
> > Thanks for your help.  
0
Utf
2/4/2010 2:10:01 PM
Reply:

Similar Artilces:

actual time and date
Hello, Does anyone know the command for adding the actual time and date to a string? thx in advance -- kizzie ------------------------------------------------------------------------ kizzie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26092 View this thread: http://www.excelforum.com/showthread.php?threadid=394484 How about ="String"&Text(Now(),"d/m/yyyy hh:mm") Rgds, ScottO "kizzie" <kizzie.1tje2f_1123657509.5167@excelforum-nospam.com> wrote in message news:kizzie.1tje2f_1123657509.5167@excelforum-nospam.com... ...

Conditional Formatting
I have a report that lists various orders for the last few months - with some as far back as a couple of years. I want to format the report so that each month has text of a certain colour, i.e. all orders from January of any year are red, Feb blue, March orange etc. How can I achieve this? Set up a table with 2 fields named MonthNumber and ColorNumber. Both of type Number. Make MonthNumber the primary key. Enter 12 records: 1-12 for the months (Jan-Dec), and the RGB value for the color you want for that month. Create a query using your table. In a fresh column of the Field row in query ...

Can I set up AutoComplete not to forget names for 30 days?
Outlook's "AutoComplete" for cc, bcc etc... seem to remember email addresses for a few days only. Can we extend the time? Thx Autocomplete does not "forget" names. The autocomplete cache stores up to 1000 names. What version of Outlook are you using? "MagicMusic9" <MagicMusic9@discussions.microsoft.com> wrote in message news:8657EEE7-74B2-4E3B-8DB8-5037F739E7F3@microsoft.com... > Outlook's "AutoComplete" for cc, bcc etc... seem to remember email > addresses > for a few days only. Can we extend the time? > > Thx > ...

Extracting a character from a string of characters
I would like to extract the 6th character (the 5) from the following example of a string of characters e.g.B123456789. Have a look in HELP index for MID -- Don Guillett SalesAid Software donaldb@281.com "Sue" <Sue@discussions.microsoft.com> wrote in message news:73FB02F4-EDA0-4819-9280-3F74F4163B4E@microsoft.com... > I would like to extract the 6th character (the 5) from the following example > of a string of characters e.g.B123456789. > > If its always the 6th character use MID i.e. if B123456789 was in cell A1 =MID(a1,6,1) -- ______________________...

Point of Sale
Kind of an odd one here - customer installs Microsoft POS and can log in and operate the store on the day of install. They close down POS that night and the next morning they are unable to log back in. They get the following error message. "You could not be logged on. Your employee ID or password may not be correct, or the computer where your store database is located may be down or otherwise unavailable." The database is on the local machine - I have since had them do a complete uninstall of POS and MSDE and perform a re-install and then update POS - applying all hotfixes...

time and curency
if i wanted to put 2 times in and get excel to work out the time take in hours between it and then times that by another cell which is th hour rate in � giving a out put in � how would i do it? i cant get excel to take the hours worked and convert it into mone earned. for example 4:55 hours and �5 hourly rate would be �24.58 but cant get excel to do this. please help thankyou Da Attachment filename: wages.xls Download attachment: http://www.excelforum.com/attachment.php?postid=62592 -- Message posted from http://www.ExcelForum.com Hi! If the earlier ...

Calculating time between successful data transmissions
Hi - I need a little help devising something that will basically do the following: I have a set of dates of transmissions and I want to find the time between successful transmits. See below: 11/11/04 6:01 DIAL 1-800-827-1159 11/11/04 6:02 Nothing was received. 11/11/04 6:04 DIAL 1-800-827-1159 11/11/04 6:07 DIAL 1-800-827-1159 11/11/04 6:10 DIAL 1-800-827-1159 11/11/04 6:14 DIAL 1-800-827-1159 11/11/04 6:17 DIAL 1-800-827-1159 11/11/04 6:18 Nothing was received. 11/11/04 6:20 DIAL 1-800-827-1159 11/11/04 6:24 DIAL 1-800-827-1159 11/11/04 6:25 Nothing was received. 11/11/04 6:27 DIAL 1-800...

Project Time & Expense
We just upgraded to GP 9.0 and BP 3.0. My manager noticed that when she returned a timesheet in Project Time & Expense, it no longer allows her to supply a note. How do we get back that option? My guess... I had to apply the setting for 'AutomaticProcessing" to 0, described in Article ID# 921642. I noticed that besides "AutomaticProcessing" there are other fields in table "PmaTimesheetConfig". One is called "ApprovalNoteSubmit". Does this turn back on the approval rejection note? Can someone list what behavior each of these fields changes? Than...

email
The time recorded on all my incoming email is clearly and unequivocally incorrect, by several hours. I haven't yet checked to see if my outgoing mail is arriving at its assigned destination also with an incorrect time. My OS (Vista) date/clock is showing the correct values. Can anyone explain why this is happening and how I might resolve this issue? I thought I was undecisive but now I''m not too sure "Pompeygill" <Pompeygill@discussions.microsoft.com> wrote in message news:4795D99B-8C65-433A-94CA-2E9AAADA619D@microsoft.com... > The time recorded on all ...

List of Missing checks for year or several months
Please tell me how I can run a report that will list only the missing checks from the register. Thanks. Great question. Hmmm. (Five minutes later he returns...) Nothing obvious appears to me. I know how I'd do it--since I don't see an obvious way to get there in Money--is that I'd pull the transactions for the account into Excel using MoneyLink. Then I'd sort by number, do some formula/copy/paste special values/ (repeat) stuff until I had it down to just unique check numbers. Then I'd do a formula looking for cases where the number is more than the previous number + 1. ...

Opening calendar (month view), I want today's date on top row
I open and use calendar in the month view. if todays date is in the last week of the month, then it is in the bottom row. That means I see all of my appointments, etc. from the last month rather than those in my future. I would like the (uncompressed weekend) month view to open with today's date in the top row... is that possible? No, its not possible. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips....

Birthdays appearing as Multi-day event
I've got a problem with Birthdays in Outlook 2003. They used to appear as an all day event with the age at that birthday in brackets next to the name. Now it appears as a multi-day event. Why has this happened and how can I solve the problem? Thanks in advance. Will Will <wgodwin@gmail.com> wrote: > I've got a problem with Birthdays in Outlook 2003. They used to appear > as an all day event with the age at that birthday in brackets next to > the name. Outlook can't do that. Pocket Outlook on a Windows Mobile PDA can, however. > Now it appears as a multi-d...

How time work with a file excel ?
Hello from Italy , i have an old five of excel (five years old) . I work with this every day , now i have the curiosity to know : how many hours it was open ? TIA Hi if I understood you correctly there is no chance to get this information :-) -- Regards Frank Kabel Frankfurt, Germany barbun wrote: > Hello from Italy , > i have an old five of excel (five years old) . > I work with this every day , now i have the curiosity to know : how > many hours it was open ? > > TIA Arbun; This is possible with a VBA macro, but I have to write that. Maybe google can serve you bett...

excel office home and student freezes all the time
I purchased office home and student 2007 several months ago. I have Vista on my computer. I had issues with Word, but was able to fix it. I have had no reason to use Exce, until now. When ever I open it, it constantly freezes up, with the little circle spinning. It says all the updates are on my computer. I have no idea how to fix this issue, and it is very frustrating, and time consuming trying to fix this issue. Thanks for you help. Steve ...

Remove time from date upon export to csv
I cannot remove the time from the date when exporting invoice data to a csv file. I have followed previous advice and created a query based on my tblInvoices table, in which I have formatted the InvDate field in the Design Grid as follows: Date: Format([tblInvoices.InvDate], "dd/mm/yyyy") (UK format) In the Query, this is fine - just the date, no time shown. I saved an export spec in the Advanced bit of the File/Export wizard. But in the resulting csv, there are six zeroes representing the time! However if I do a manual export by following exactly the same process bu...

ComboBox DropDown Size: Design vs Run Time?
Hi, I was wondering, what's the reason for specifying the dropdown size of a combobox at design time? I'd say it's better to make the size as large as possible to display as much rows (and columns) without falling of the screen. Olaf van der Spek wrote: > Hi, > > I was wondering, what's the reason for specifying the dropdown size of a > combobox at design time? > I'd say it's better to make the size as large as possible to display as > much rows (and columns) without falling of the screen. Sounds reasonable. Are you having some kind of issue wit...

Reorder transactions within same day
Hi, I import QIF statements into my accounts in Money 2006. Occasionally I match a transaction with an upcoming bill, which usually causes the sequence of transactions in the register within the same day to change. How can I reorder these transactions to match my bank statement? I have tried the following: - I dictate the sequence using numbers (1, 2, 3, ...) in the transaction "number" field thereby manually setting the sequence for that day. This works but is not elegant (numbers will not be unique) - I "cut and paste" the transactions to a "Clipboard" ac...

SelStart Error
I have been using input masks for dates on forms. I usually add code to force the cursor to the beginning of the text box using SelStart. Private Sub txtDate1_GotFocus() Me.txtDate1.SelStart = 0 End Sub I created a form and the above field functioned fine. After I added more controls and code I began to get a Run-time 2185 error -You can't reference a property or method for a control unless the control has the focus. Anyone know what causes this or is my form corrupted in some way? Thanks in advance for any help or insight, -- Scott S Scott, the only case that comes to mind ...

Budget Items do not appear in Monthly Budget Report
Hello, I've been using Money for nearly a decade to track account details and that has worked fine. Now, I've decided to start using the budgeting functionality and am extremely frustrated. I have items that show in the budget, but when I view the Monthly Budget report they are not included. What gives? The item is a simple mortgage payment setup as a "Loan Payment" bill from checking to the mortgage loan account. Please help! Thanks! Ian "R. Ian Lee" <RIanLee@discussions.microsoft.com> wrote in message news:C07484ED-52E3-4FA8-87EF-EB0679821972@...

Appointments and Time Zones
When i enter a new appointment into the the Outlook calendar, is it possible to specify the time zone for the appointment? "CD" <CD@discussions.microsoft.com> wrote in message news:852BE35D-310E-4FF3-8ABB-FD104D6915B8@microsoft.com... > When i enter a new appointment into the the Outlook calendar, is it possible > to specify the time zone for the appointment? With Outlook 2007 and later, yes. -- Brian Tillman [MVP-Outlook] ...

Days in month for 10 year period
What is the quickest way to get the days in a month in a series. Eg 31 Jan 2005 28 February 2005 Put this in A1 =DATE(2005,ROW()+1,0) And drag down 120 rows. Format the cell as: dd mmmm yyyy Geoff wrote: > > What is the quickest way to get the days in a month in a series. Eg > > 31 Jan 2005 > 28 February 2005 -- Dave Peterson bit unclear myself, but maybe format as ddd dd mmm yyyy -- HTH RP (remove nothere from the email address if mailing direct) "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message news:427B64F1.622B2742@netscapeXSP...

conversion to time
Problem concerning the conversion of the following. Table input is as follows: Delay //Input time as a number with inputmask 01:23 00:00;0;_ 00:23 00:14 I need a conversion so that in the report the average time is displayed as 00:53 hours. I have the following: When is display the total time, the total time is displayed as 53.333. How do I tell Access that the input is a time input? Thanx Martijn martijnbomhof@hotmail.com ...

Handling open sales orders w deposits at month end
We are having a debate on how to handle these. The debate centers over doing a customization that will remove the deposits, close the orders, recreate the orders in the new month and assign the deposits back to the orders. The other side of the debate is there has to be a better way to handle this as it shouldnt be uncommon for businesses using GP to have open orders cross months. (As a business we commonly have orders that will be placed in one month and then completed/shipped several months later). Does anyone have any input for out debate? We leave the open orders on the month it wa...

Seems like time to dredge this one up
Many of you have already seen this, I'm sure, but recent threads about M+ vs. M06 and HSA support in Money and the reuse of this reused music in a new commercial bring it back to mind. If you haven't seen it already, enjoy. If you have, well, enjoy anyway. How products like Money get to be products like Money: http://www.youtube.com/watch?v=aeXAcwriid0. "Dick Watson" <littlegreengecko@mind-enufalready-spring.com> wrote in message news:%23bpPLt1HIHA.5208@TK2MSFTNGP04.phx.gbl... > Many of you have already seen this, I'm sure, but recent threads about M+ ...

Connection hang time when sending from Outlook Express
I recently set up new email accounts for everyone in our office after we got a domain name that included email addresses. We also recently switched from dialup to cable modem and set up a wireless network. Our domain name and email accounts are through earthlink but our high-speed cable is through optimum online. We are all using Outlook Express 6. When sending messages, Outlook Express spends a long time (20-30 seconds) connecting before it sends the message. The actual send/receive is fast, but this hang before sending is annoying. Can anyone help? Thanks. - Eric ...