Formatting a date field

Is it possible to format a date field in excel such that :-
1) it accepts day, month, year or month, year or just year or blank and
2) you are able to sort properly on the field

Bob M 


0
matthews1 (3)
5/8/2007 8:10:47 AM
excel 39879 articles. 2 followers. Follow

1 Replies
318 Views

Similar Articles

[PageSpeed] 5

Formatting has no effect on XL's input parser.


You may find you can work with this:

   http://cpearson.com/excel/DateTimeEntry.htm


In article <f1pb6t$bik$1@lust.ihug.co.nz>,
 "Bob Matthews" <matthews@es.co.nz> wrote:

> Is it possible to format a date field in excel such that :-
> 1) it accepts day, month, year or month, year or just year or blank and
> 2) you are able to sort properly on the field
> 
> Bob M
0
jemcgimpsey (6723)
5/8/2007 9:37:58 AM
Reply:

Similar Artilces:

How to convert two dates in to HH:MM:SS
Hi, I am trying to calculates the two dates cells diffrence int hour:minutes:seconds. could some help me in finding a solution i Excel sheet. eg. Cell A1 = Jan-01-2004 00:00 Cell B1 = Jan-02-2004 23:30 Cell C1 = 47:30 looking for a result like 47 hours 3 min. thanks sa -- sashaik ----------------------------------------------------------------------- sashaikh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1555 View this thread: http://www.excelforum.com/showthread.php?threadid=27117 One way: C1: =B1-A1 Format C1 with Format/Cells/...

Conditional Formating 4 conditions
I need to set up 4 conditions. Column F is Date Due, column G Date Complete, and Column H is Status. I want column H to change pattern color and insert the color name if column F is past the Date Due to be red, yellow if 5 days before the date due, white if date due is on target, and green if a date is entered into the date complete column. My formula is if(f2<=today(),"RED",if(f2<=today()+5,"YELLOW",if(f2<=today()+365,"WHITE,if(g2<>f2,"GREEN" How can I do this? the red, yellow, and white seem to work, but I can't get the green t...

Help formatting text color based on formula
I have searched the forum and find some things that help but not quite enough. I have a table of numbers. In fact there are 300 rows of 5 colums of numbers. What I would like to do is to change the color of the number if it matches one of 5 other numbers. Example Reference #s 2 6 23 43 48 01 02 09 08 14 06 14 23 43 44 03 11 23 24 52 etc etc etc etc The example list is already populated. When the reference numbers are entered I want any number in the example list that matches any number in the reference list to change to a specified color. Any way to...

Book1
1- I deleted any XLT files in the XLStart folder. 2- I open Excel 3- I make my default settings in the formatting toolbar so that the border, fill color etc have the default options that I want in the toolbar. 4- I then save the book as Book.xlt in the XLStart folder 5- When I get back in to Excel the Format Toolbar is back to the original settings. Maybe we cannot preset the formatting toolbar to certain settings automatically. Thanks for everyone's help. MD -- mdalby ------------------------------------------------------------------------ mdalby's Profile: http://www.excelforu...

Adding a previously removed field to a form
Hi I have removed a field from the contact form (the state field) and want to re-add it. It does not show in the list of fields to add, presumably because CRM thinks that it is already on the form. How do I convert a field from a text field to a pick list? Does it require adding an entirely new field to the schema and using this field? Thanks Gill did you publish your change via deployment manager then do an iisreset command? "Gill Walker" <anonymous@discussions.microsoft.com> wrote in message news:064301c39d01$352f5b50$a401280a@phx.gbl... > Hi > > I have...

Make the SmartList field 'Visible To' default to 'User ID'
I have several requests from clients that I train who want the SmartList field 'Visible To' to have the default 'User ID' rather than 'System'. The main reason is that users forget to change this option and then others are looking at their smartlists and sometimes delete them. Also, it would be good if we could have this as a security restriction so only particular users could create SmartLists for themselves and not be allowed to create them with the 'System', 'Company' or 'User Class' options. Thanks again. -- Microsoft Certified Train...

Simple Date/Time Macro
ActiveCell.FormulaR1C1 = "9/16/2009" Range("B1").Select ActiveCell.FormulaR1C1 = "9:25 AM" Range("C1").Select End Sub I want to set up a simple workbook that enters the above information on the current line in the sheet. When I recorded the keystrokes I wanted, I ended up with the following macro, which only works once. Starting with the second line, running the macro puts the date in the right place, skips Column B and then leaves the cursor in R1C1. How do I get this macro to enter all the information on the current line (date in c...

Print format changes when saving as a PDF
Not sure if this is a Publisher question or Adobe question but I have made several flyers in Publisher and when I save them to PDF, the page setup modifications go back to default. Any ideas? What do you mean *back to default*? Are you using a template? You might save the file first and then convert. What version Publisher? Are you using Acrobat? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Joey" <Joey@discussions.microsoft.com> wrote in message news:18CF932A-A885-40AA-9876-7AA84F86C436@microsoft.com... > N...

How do I eliminate, in Excel XP, "too many cell formats" error?
See: http://support.microsoft.com/kb/213904/ HTH Jason Atlanta, GA >-----Original Message----- > >. > Some info: XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=213904 A few people have posted that they could open the file in OpenOffice, then remove some of the formatting, save it and then excel would open that file ok. http://www.openoffice.org, a 60-65 meg download or a CD And it really makes it easier for most people if you put the question in the body of your post--not just the subject. Dennis wrote: -- Dave Peter...

Code instead of copy n' paste the conditional format by hand
Hi there! If you know this trick, please help me! I have killed a lot of time... To be able to keep formats in my B:B row, I use the contitional format function. When I use a relative formula like =$A6="I" everything work fine ( yellow interior color when "I", Black interior when "K".) . I just copy the format manually to all cells and the relative row fill out the area. However, I had to create code for it, running everytime with "Sub Worksheet_Change". This because I need to keep the formats when i paste text from other worksheets. If I have code ac...

debt reduction planner doesn't work (wrong date)
Hello guys, I have been using Microsoft money 2005 (international version) for a long time now. Problem is that for the last 2 years I have noticed that the debt reduction planner (under planning) doesn=92t work. I haven=92t paid any attention until now that I have decided to try again. What I do is to add a credit card. When I move into plan my credit card, whatever amount of money I put on =93monthly allocation on debt=94 or on =93one time extra payment=94 the date on the result below never changes. It always show me that I will be out of debt in for example 22 June. The date doesn=92t chan...

Formatting envelopes
Hi, I posted this question before, but I don’t think I asked my question correctly. I have a table with name and addresses and another table with envelope sizes (width and height in inches). Is it not possible to select a LETTER size paper in LANDSCAPE mode and print envelopes using the width and height to calculate the margins needed to print on the selected envelope? Another question? When printing in landscape mode do all printers print the page from left to right? If not, my idea will not work. I would really like to get this to work and your help would be greatly appr...

Date Field conundrum
I have two tables of data joined in a union query, each of the tables have an admit date and a birthdate field and the field type is date/time defined. I have placed the resulting query in a new query in an attempt to return only those records where the admit date and birthdate field are equal. I have tried a number of different criteria but no matter what I've tried I get data type mismatch etc type error messages. I have also tried using the resulting recordset in a new query and qualifying each of the fields with CDate but still cannot get a result without an error. I have t...

XmlSerializer for optional xs:date #2
I have several elements that have optional child elements and attributes that are xs:dates. I have been using System.DateTime and the XmlAttribute/XmlElement attributes, but these will never work as System.DateTime can never be null. So public class MyClass { [XmlAttribute("a")] public string a; [XmlElement(ElementName="b", DataType="date")] public DateTime b; } will return <MyClass> <b>0001-01-01</b> </MyClass> when I want an empty element. I have tried my own class (Date) that exposes an XmlText attribute: public cl...

How to get current date & time in a specific time zone
Hi folks, How can I get the current time of a specific time zone? For example, my workstation was set in "(GMT+08:00 Beijing, Chongqing, Hong Kong, Urumqi)", how can I get the relative datetime in "(GMT-06:00 Central Time (US & Canada) with daylight saving changes". Rgds, Marco >How can I get the current time of a specific time zone? Marco , Have a look at the TzSpecificLocalTimeToSystemTime & SystemTimeToTzSpecificLocalTime APIs. Dave ...

Report that calculates based on another field's criteria
I have created a report that shows our plant's production for each machine and each product made. We often run "Trial" products that are new developments on the machines. For each grade/machine I have a [Standard] production that the machine operator is expected to produce during his shift. Trials are an exception, because we don't know how much we should be producing since they are new products. I want my report to filter out all "Trial" products when calculating the [Total Production] and [% of Standard] achieved during a shift. How can I do this? ...

(2003) remove days and date from preview pane
In Outlook 2003 it now groups my emails according to date recieved, for instance, in my inbox I have a grouping for emails received "Yesterday" and "Wednesday", etc.. How do I remove this feature? I want my emails to simply be listed in order WITHOUT the Date headings above each day (or week, or month). TIA (-WW) View | Arrange By - uncheck the option to show in groups. -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www.slipstick.com? -Please post your Outlook version! "W i l l" <willis3140@y...

formatting the column/bar of a graph
How do I set the transparency of a bar or column in a graph. Have gone to Format Data Series - Fill effects, but the transparency is greyed out. Method 1: On the Patterns tab Area section choose None Or Method 2 1. Select an empty cell 2. Hold down the Shift key and choose Edit, Copy Picture, OK 3. Select the series on the chart and choose Paste. -- Cheers, Shane Devenshire Microsoft Excel MVP "WitchLady" wrote: > How do I set the transparency of a bar or column in a graph. Have gone to > Format Data Series - Fill effects, but the transparency is greyed out. That s...

After date entered, prefill another date field
Hello, I was wondering if anyone knows how to get one field to populate the same datainto another field? In other words: I have a date field "Funding Date" and when the user enters the funding date, I want the txtDateFrom1 field to automatically populate with the same date so the user doesn't have to enter it twice. If possible, please let me know and also, please simplify answer. Thanks!!! Use the After Update event of the Funding Date control: Me.txtDateFrom1 = Me.FundingDate -- Dave Hargis, Microsoft Access MVP "Stockwell43" wrote: > Hello, > ...

email date range to pst file
Is is possible to extract or put emails from a say January 1st 2006 to August 1st 2006 to a pst file? On Fri, 4 Aug 2006 09:51:46 -0700, "tony" <none@none.com> wrote: >Is is possible to extract or put emails from a say January 1st 2006 to >August 1st 2006 to a pst file? > Yes yes...using the Options... -- Susan Conkey [MVP] "tony" <none@none.com> wrote in message news:#Ze8GZ#tGHA.1512@TK2MSFTNGP03.phx.gbl... > Is is possible to extract or put emails from a say January 1st 2006 to > August 1st 2006 to a pst file? > > ...

Change the formatting of row by select a single cell & Editing should be working #2
Hi All; I require such a sheet code which can help me in Changing the formatting of row (like A1:A25) of selected cell (A4 is Selected) and allow me to use editing (Cut, Copy, Paste, Undo etc). Currently I'm Using following code but this does not allow me to use editing (Cut, Copy, Paste, Undo etc) and I can not decide the range for working this sheet code. Any kind help is appreciated. Private Sub Search_Click() Sheets("Quick Search").Visible = True ActiveWindow.SelectedSheets.Visible = False Sheets("Quick Search").Select End Sub Private Sub Workshe...

Effective date for pay rate increases
Hi all! My client does not use the post dated pay rate feature in GP to record salary increases/changes. Instead, they go directly to the employee pay code maitnenance and change the pay rate there. Is there a way to tell when the pay rate change was enforced? Any report or field that can be used in a report? Any ideas will be greatly appreciated. Thanks -- Marisol Mortera Unfortunately, not. They would need to look at purchasing HR or an audit tool, like one from Rockton. -- Charles Allen, MVP "Marisol" wrote: > Hi all! > > My client does not use the p...

Export all calendar items in Vcard format
Hello, I can save a single calendar entry in vcard format. is it possible to export all my calendar entries in vcard format. Outlook provides the export to csv, I was wondering if there was a way to do the same thing in vcard. Thanks Nik ...

Aged Trial Balance as on a particular Date
Hi, When I print the Aged Trial Balance report in purchase module as on a particular date, it does not filter the transactions, instead it shows up transactions beyond the filtered date. Both the document date and posting date of the transactions getting displayed are after the filtered date. For example, I give 31-12-2008 as the date in the "Pint / Age as of: Enter Date" option. The report output shows up transactions with Document Date and Posting Date after 31-12-2008. Can anyone tell me what could be the reason? Thanks & Regards, Perumal Perumal, You need to us...

US-UK date formats & downloading transactions
I have accounts in both the US (=base currency) + UK. I typically download transactions manually from the institutions I hold accounts with, but the US-UK date format difference is causing problems (eg. 01/10/05: in the US = Jan 10th, in the UK = Oct 1st). I can't find a way of dealing with this. Help? In microsoft.public.money, "Anthro girl" <Anthro girl@discussions.microsoft.com> wrote: >I have accounts in both the US (=base currency) + UK. I typically download >transactions manually from the institutions I hold accounts with, but the >US-UK date form...