Auto fill day after date is entered?
Can this be done? I want to enter a date on a form and have the proper day appear in the next box after it. Should I create a calendertable? Suggestions? TIA -- Logo
|
0 |
|
Utf
3/22/2010 4:47:01 AM
access.forms
6864 articles. 
2 followers.
8 Replies
1910 Views
On Sun, 21 Mar 2010 21:47:01 -0700, Logo <Logo@discussions.microsoft.com>
wrote:
>Can this be done? I want to enter a date on a form and have the proper day
>appear in the next box after it. Should I create a calendertable?
>Suggestions? TIA
The day, as in Monday or Tuesday? You don't even need a separate textbox
(though you can have one if you wish); if you set the Format property of a
textbox on a form to "Long Date" or to a custom date format which displays the
day name (for instance "mm/dd/yyyy dddd") and type a date, in any format,
you'll see the day name. E.g. typing
3/22
into a textbox with the custom format will accept the date and display
03/22/2010 Monday
If you're trying to STORE the day name in a separate field from the date...
don't!!! It's redundant, unnecessary, and almost surely useless.
--
John W. Vinson [MVP]
|
|
0 |
|
John
3/22/2010 5:05:53 AM
"John W. Vinson" wrote: > On Sun, 21 Mar 2010 21:47:01 -0700, Logo <Logo@discussions.microsoft.com> > wrote: > > >Can this be done? I want to enter a date on a form and have the proper day > >appear in the next box after it. Should I create a calendertable? > >Suggestions? TIA > > The day, as in Monday or Tuesday? You don't even need a separate textbox > (though you can have one if you wish); if you set the Format property of a > textbox on a form to "Long Date" or to a custom date format which displays the > day name (for instance "mm/dd/yyyy dddd") and type a date, in any format, > you'll see the day name. E.g. typing > > 3/22 > > into a textbox with the custom format will accept the date and display > > 03/22/2010 Monday > Awesome! But I do need to have the day pop up in the next textbox. I tried to use the Format property to extract it after setting the control source to the textbox before it but was unable to get it to only show the day. How do I do that? > If you're trying to STORE the day name in a separate field from the date... > don't!!! It's redundant, unnecessary, and almost surely useless. > I agree it's redundant, but hardly useless! It enables my users to see if they are entering data with the correct date. If the day pops up wrong, they know their date is off. As it is now, when they enter the date then enter the day, they may have the day correct but the date wrong. This will enable them to instantly see if they have the correct date. Thank you very much! This has been a huge issue with us for a long time! Logo > -- > > John W. Vinson [MVP] > . >
|
|
0 |
|
Utf
3/22/2010 12:32:01 PM
On Mon, 22 Mar 2010 05:32:01 -0700, Logo <Logo@discussions.microsoft.com>
wrote:
>Awesome! But I do need to have the day pop up in the next textbox. I tried
>to use the Format property to extract it after setting the control source to
>the textbox before it but was unable to get it to only show the day. How do
>I do that?
Why in *THE NEXT TEXTBOX* rather than in *THE VERY SAME TEXTBOX*?
You clearly did not try my suggestion, because *it will work*.
Again: open the form in design view. Select the textbox. Find the Format
property. In the Format property type
"dddd dd/mm/yyyy"
If the user types 3/22 into the textbox on the form and tabs out of it, the
textbox will change its appearance and will now show
Monday 03/22/2010
This does not need any code; it does not need the Format() VBA function; it
does not need a second textbox; and it *does* enable them to instantly see if
they have the correct date.
Perhaps you could explain why you find this solution unacceptable.
--
John W. Vinson [MVP]
|
|
0 |
|
John
3/22/2010 3:54:01 PM
On Mon, 22 Mar 2010 09:54:01 -0600, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
>Again: open the form in design view. Select the textbox. Find the Format
>property. In the Format property type
>
>"dddd dd/mm/yyyy"
sorry, should have been
dddd mm/dd/yyyy
--
John W. Vinson [MVP]
|
|
0 |
|
John
3/22/2010 5:04:43 PM
> > Why in *THE NEXT TEXTBOX* rather than in *THE VERY SAME TEXTBOX*? > > You clearly did not try my suggestion, because *it will work*. > I absolutely tried your suggestion! It worked great! So what do I do with the day field now? I didn't design this database, but I am looking for ways to make it better. I guess just delete the day field? > Again: open the form in design view. Select the textbox. Find the Format > property. In the Format property type > > "dddd dd/mm/yyyy" > > If the user types 3/22 into the textbox on the form and tabs out of it, the > textbox will change its appearance and will now show > > Monday 03/22/2010 > > This does not need any code; it does not need the Format() VBA function; it > does not need a second textbox; and it *does* enable them to instantly see if > they have the correct date. > > Perhaps you could explain why you find this solution unacceptable. > Thank you for your answer, it is well appreciated and very acceptable. But perhaps a suggestion of what I should do now with my day column might have been a better response than repeating your last reply. I apologize for my ignorance, I am still extremely new at this. Logo
|
|
0 |
|
Utf
3/23/2010 2:16:01 AM
On Mon, 22 Mar 2010 19:16:01 -0700, Logo <Logo@discussions.microsoft.com>
wrote:
>I absolutely tried your suggestion! It worked great!
>So what do I do with the day field now? I didn't design this database, but I
>am looking for ways to make it better.
>
>I guess just delete the day field?
If there is a day field *in your table*, then yes, delete it; it's redundant.
If you have a Date/Time field in the table, that field contains the
information needed to display the name of the day.
If you had both fields in the table, then you could get anomalies such as
#3/22/2010# stored in the date field, and Thursday stored in the
day-of-the-week field: one or the other has to be wrong, and there's no easy
way to tell which!
--
John W. Vinson [MVP]
|
|
0 |
|
John
3/23/2010 3:57:55 AM
"John W. Vinson" wrote: > > If there is a day field *in your table*, then yes, delete it; it's redundant. > If you have a Date/Time field in the table, that field contains the > information needed to display the name of the day. > > If you had both fields in the table, then you could get anomalies such as > #3/22/2010# stored in the date field, and Thursday stored in the > day-of-the-week field: one or the other has to be wrong, and there's no easy > way to tell which! > -- And that has been my dilemma all along! Thank you so much for solving this! Now, is there a way to display 3/22/2010 Tuesday instead of Tuesday 3/22/2010? Thanks again! Logo
|
|
0 |
|
Utf
3/23/2010 6:09:01 AM
On Mon, 22 Mar 2010 23:09:01 -0700, Logo <Logo@discussions.microsoft.com>
wrote:
>"John W. Vinson" wrote:
>
>>
>> If there is a day field *in your table*, then yes, delete it; it's redundant.
>> If you have a Date/Time field in the table, that field contains the
>> information needed to display the name of the day.
>>
>> If you had both fields in the table, then you could get anomalies such as
>> #3/22/2010# stored in the date field, and Thursday stored in the
>> day-of-the-week field: one or the other has to be wrong, and there's no easy
>> way to tell which!
>> --
>
>And that has been my dilemma all along! Thank you so much for solving this!
>
>Now, is there a way to display 3/22/2010 Tuesday instead of Tuesday 3/22/2010?
Of course:
mm/dd/yyyy dddd
You'll need to dig some, but the Access Help topic for Format has all this.
You can construct a date format out of pieces - "d" is the day of the month
(e.g. 3), "dd" is the two-digit day (03), "ddd" the three-letter day
abbreviation (Tue), "dddd" the full day name (Tuesday), "yy" is the two-digit
year (10), "yyyy" the four digit year (2010), and so on and so on.
--
John W. Vinson [MVP]
|
|
0 |
|
John
3/23/2010 3:50:40 PM
Similar Artilces:
Help with computing date\Time between previous record
I am trying to calculate how long it has been from a previous visit. I am tracking visit dates for customers. So I am sorting visit dates by customer. I want to look at a record date and subtract the previous reocrds date from it for all dates in the db. So in the end there is another Field that shows how long it has been bewteen customer visits? Dave K Use in your query: Datediff("d",[firstdatefield],[seconddatefield]) The "D" is for days. -- Milton Purdy ACCESS State of Arkansas "Dave" wrote: > I am trying to calculate how lo...
Auto scroll with CDHtmlDialog #2
[this is a re-post of the post I made August 10. I tried setting up my MSDN email alias again with a new alias to see if it makes a difference] I am working on a little MFC 7.1 program for a friend and one thing I want to do is load HTML into a fullscreen window. I want the contents of this window to smoothly scroll automatically (no user input) and when it reaches the bottom to pause for a few seconds and then jump back to the top. So I need to know: 1. How to automatically vertically scroll the CDHtmlDialog window smoothly without any user input (with a user adjustable rate). 2. How ...
PivotTable
Hi, I was wondering if there was any way to get a range or multiple dates when you filter using a pivottable. As it is right now, There is "All", "Blank" and each individual date but not the ability to pick multiple dates or a date range (12/1 - 12/31) I currently am running the spreadsheet from Microsoft Office XP 1. Make sure the dates are excel dates (numeric) then after applying the pivot right click a date in the pivot table and select group, then group by day AND month -- Regards, Peo Sjoblom "Z" <arthursdomain@gmail.com> wrote in message news...
Date/Text Formatting
Hi, I have a column with in date format and would like to get this into a text format as follows : Column would say : 01/03/04 I want text to say : 'The month is March' Any help would be appreciated... Thanks Tappie One way .. Assuming dates are in col A, A1 down Put in B1: ="The month is "&CHOOSE(MONTH(A1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oc t","Nov","Dec") Copy down -- Rgds Max xl 97 --- Please respond in thread xdeme...
inactive auto
<a href="<=======}==O"><=======}==O</a> ------------------------------------- How can I Automatic item Inactive when it goes to 0 on hand, is there anyway. can anyone help me. Also I need to know how not let the cashiers change the item on hand. tahnk you ##-----------------------------------------------## Newsgroup Access Courtesy http://www.rockryno.com/ Tax and Accounting Software Forums Web and RSS access to your favorite newsgroup - microsoft.public.pos - 39423 messages and counting! ##-----------------------------------------------## ...
auto sort macro
I am trying to use a macro to sort a table of data on worksheet 3, whenever I update a value on worksheet 1 using Excel 2003 (Copy of macro below). Although the macro picks up the change in data on sheet 1 and runs the sort subroutine, nothing happens. I have discovered that by moving the tables to sheet 1 and repointing the macro to the data that it does work and while this is sufficient, I would like to know why the macro does not work when the tables are on sheet 3. Thank you for any advice anyone can provide Private Sub Worksheet_Change(ByVal Target As Range) If Not ...
How to stop Excel from auto updating chart?
Hi, I have written a VBA that will find some averages for me. It'll output the averages in the same range of cells no matter which monthly/seasonal averages I wanted. Is there a way to make the chart so that let's say...after I run the script to calculate the winter averages, then make a chart of it on a worksheet, when I re-run the script for summer, the chart wouldn't update to the summer data? In another word, can I have the chart to not update? I know under there's Tools->Option->Edit->Ask to update automatic links but I don't think this is what I'm loo...
Genrating text and auto number
How to generate auto number but i want it to be combination with text Example L0001, THEN L0002 and so on..how? -- Message posted via http://www.accessmonster.com Emily, What does the 'L' stand for ? Will this field contain other series of numbers that start with some other letter but which must also start sequencing at 00001? By storing more than one piece of information in a single database field, you are violating the first normal form of database design. I would recommend you store the letter designator in a separate field. Assuming that you have other letters, that mu...
Automatic Populate Todays Date in Cell when File is Saved.
I am creating a user data entry spreadsheet in excel and I want to record the date in a specific field when the excel file is opened. I need the field "Submit Date" to be automatically populated and protected when a user opens the file and performs a save. If the user does not Save the file the date field should remain empty. Can anyone help with this please? Paste the following macro into the Workbook module. You access the Workbook module by right-clicking on the Excel icon to the left of the word "File" in the menu across the top of the spreadsheet, select View C...
Date prompt appearing twice due to chart in report
Hi All, I am creating a report based on a query. I have a prompt to ask for the Beginning Date and the Ending Date. When I run the query I get the correct results. I also get the correct results when I run the report. However, I have just added a pie chart in my report footer, and because of this, I am always forced to enter the beginning and ending dates twice when running the report. What do I need to do to make the prompt only appear 1 time for each date? Thank you! decklun The prompt appears each time Access needs to run the query. Once for the report and a second time for ...
Count of Weekdays Between Two Dates
Task: With a given start date and end date, print a list of Months showing a count of weekdays and weekend days in each. It seemed like such a simple request... but how to get started? If I already had a table of all dates between the start and end dates, I'd have it whipped. Any thoughts appreciated. -- croy Here are some ideas you can use for this. First, you need to know how may days are in the month. Then you can use the function below to return the number of work days in the month. The subtract the work days from the number of days, and it will return the week end days. ...
dATES IN BOXES
hi All: THis is just killing me; why is it that when I enter in a number into my spreadsheet the it comes up with month attached. For examply when I enter in the number 4 it populates the field with 4-Jan. Please help this is just a real pain in the asss Mark, Does this happen in every cell or just a few. The formatting of the cell is probably set to a date format. Select the problematic cells, go to the Format menu, choose Cells, then the Number tab, and choose General. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chi...
Starting mileage on 1st day of month, ending mileage on last day of month
Hello! I have a table set up with Date, Vehicle Number, Start Mileage and End Mileage. I want to be able to pull up the start mileage on the 1st of the month and the end mileage on the last day of the month. I have read through other posts and can't seem to put my finger on what I'm looking for. Thank you in advance for the help!! -- Message posted via http://www.accessmonster.com The structure you described (Date, Vehicle, Start, End) implies that any date, vehicle, start and/or end can be entered. What do you mean by "pull up the start mileage on the 1st of the month&...
Auto Calculate Question / HELP!
When I click on the fill handle to drag the data down the cells fill with data grabbed veritcally, for example: When I drag the fill handle down the formulas look like: =D21 =D22 =D23 =D24 =D25 But is there a way for Excel to be adjusted so that they attach the information laterally instead of veritcally? For example, I want to drag the auto fill DOWN but want the cells to read: =D21 =E21 =F21 =G21 =H21 Thank you! Try this... Assume you enter the first formula in cell A1 =INDEX(D$21:H$21,ROWS(A$1:A1)) Drag down as needed. Adjust for the correct end of range ...
email date stamp
hello all Can you tell me whether there is anyway to change the Outlook email stamp to a different format All emails from everyone all seem to have the following format Sent: Wednesday, August 23, 2006 6:10 PM for recording when the email was sent. All emails I send use the same format. A colleague of mine has told me that he can make Outlook stamp all his outgoing emails with the following format: Sent: 2006-08-21 05:28 Can individuals control this? if so how? In your display? View-> Arrange By-> Current View-> Customize Current View-> Format Columns... -- Robert Sparn...
Date expressions
Hello I am just wondering if there is an easy way that an expression can be used to calculate the number of days in a month? Any assistance would be greatly appreciated Thank you in advance AC well, the number of days in a month can change - specifically, the number of days in February, depending on what year it is. for the number of days in a *specific month of a specific year*, the following will work, as Public Function isDaysCount(ByVal dat As Date) As Integer Dim dat2 As Date dat2 = DateAdd("m", 1, dat) isDaysCount = DateDiff("d", DateSerial(Year...
Loses "All day event" option
Quite frequently all my appointments marked with "All day event" will forget this option and become a 2 day event. This happens whenever I change my computer's timezone setting. Is there a way to tell Outlook that I really want an appointment to be an "All day event" regardless of the timezone? I travel and switch timezones frequently. Even the holidays are getting screwed up. Using Outlook 2003 SP2. Thanks. What do you expect to happen when changing your time settings in Windows? It affects every application that uses time zones for its information. Why not use...
Want to avoid pasted text becoming dates
Occasionally, I will paste from a table arrangement on a website. Sometimes, this data will contain text that has some characteristics of a date, eg, 3/16 as a fraction. I could live with it becoming 0.1875, but not March 16, 2005. How can I prevent Excel's intelligence from overcoming my stupidity <g>. TIA, George One work-around is to paste into notepad. Save that file as *.txt Then open that text file in excel. You'll be prompted by the text import wizard for how you want each field. GeorgeB wrote: > > Occasionally, I will paste from a table arrangement on a w...
auto number columns in excel
Is there a way to set up a column of numbers so that it automatically updates the numbers every time a line is added or deleted? >-----Original Message----- >Is there a way to set up a column of numbers so that it automatically updates >the numbers every time a line is added or deleted? >. >Use =count(A1:A10) or more rows as required. If its the last row used - =row(B1) extended down to maybe B100. Then you can use =large(B1:B100) this will give you the last row used. Mal I either did not understand the reply given or was not clear in what I am trying to accom...
Auto Accept not removing cancelled meetings
The Auto Accept agent (running on E2k3 server) is not removing cancelled meetings from my resource calendars. Although the resource will let users book over the cancelled meetings, the calendar still shows the time slot as busy in the attendee availability view. If you go directly to the resource calendar, it will show the meeting as being cancelled. Am I missing a config somewhere that would totally remove the cancelled off the recource? Thanks in advance for any help! If you want a product that works properly, check out http://www.swinc.com/erm. -- Ed Crowley MVP - Exchange &qu...
Archive Modified date
QQ, I have just moved users from one mail server to another. Now, archiving no longer works. I looked at this and found out that archiving works with modified dates and since this was changed recently I am stuck. Do you guys have any idea, if their is a work around for this. Users have lots of sub folders. Thanks B Not really...you can manually archive by dragging and dropping items to the archive folders, or you might be able to use one of the 3rd party solutions listed at http://www.slipstick.com/addins/housekeeping.htm. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to m...
Follow-up: Dynamic 90 Day Running Average
That works beautifly - almost. It will give me the 90 day average for today. Very nice, thank you. Is there a way to generate a 90 day average for each day in the database? PS - when I started writing this "Frankenstein," it was just designed to keep attendance, so the field names weren't that important. (Poor form, I know. But at the time, I had never used Access before [old DBMS programmer of FoxPro and dBase].) I want to change it, and will take the time to do that here soon...or write the whole thing in VB. :D Thanks again. "Ofer Cohen" wrote: > L...
Outlook 2007 & "Enter Network Password" Popup?
Has anyone that has been using OL 2007 experienced the "Enter Network Password" popup windows? I was told by my ISP that it is an Outlook 2002 issue only. Can anyone dispute that? Thanks Oops "<--Mardman-->" <zeus661@yahoo.com> wrote in message news:89adnXUtn8zh0xnYnZ2dnUVZ_sKunZ2d@buckeye-express.com... > Has anyone that has been using OL 2007 experienced the "Enter Network > Password" popup windows? I was told by my ISP that it is an Outlook 2002 > issue only. Can anyone dispute that? Thanks > I'm also seeing th...
Auto Insert Parenthesis
How do I auto-insert parethesis around prepopulated data in a worksheet? I have a column of information that only if there is information, then I would like it to be in parenthesis. For example, A1: data B1: data C1: no data D1: no data E1: data I would like to auto-insert parenthesis around the cells that show "data" but to ignore cells that have "no data". This would involve only 1 column in the excel file. The cells that contain data would look like this, example: (Smith) or (Jones) rather than Smith or Jones. Sub parens() Dim rng1 As Range ...
Date Calculations
I have to create a formula in excel that takes a date (4-1-2010) and subtracts another date from it (2-1-2008) and gives me the remainder of months. Any ideas? How do you define a month difference? 30 days? Calendar months? What about months with different numbers of days. E.g., how many months between 28-Feb and 31-March. 1? 1+3/30? You need to define how the months should be calculated. At its simplest, just subtract one date from the other and divide by 30. That will give one of several possible answers. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel,...
I am trying to calculate how long it has been from a previous visit. I am tracking visit dates for customers. So I am sorting visit dates by customer. I want to look at a record date and subtract the previous reocrds date from it for all dates in the db. So in the end there is another Field that shows how long it has been bewteen customer visits? Dave K Use in your query: Datediff("d",[firstdatefield],[seconddatefield]) The "D" is for days. -- Milton Purdy ACCESS State of Arkansas "Dave" wrote: > I am trying to calculate how lo...
Auto scroll with CDHtmlDialog #2
[this is a re-post of the post I made August 10. I tried setting up my MSDN email alias again with a new alias to see if it makes a difference] I am working on a little MFC 7.1 program for a friend and one thing I want to do is load HTML into a fullscreen window. I want the contents of this window to smoothly scroll automatically (no user input) and when it reaches the bottom to pause for a few seconds and then jump back to the top. So I need to know: 1. How to automatically vertically scroll the CDHtmlDialog window smoothly without any user input (with a user adjustable rate). 2. How ...
PivotTable
Hi, I was wondering if there was any way to get a range or multiple dates when you filter using a pivottable. As it is right now, There is "All", "Blank" and each individual date but not the ability to pick multiple dates or a date range (12/1 - 12/31) I currently am running the spreadsheet from Microsoft Office XP 1. Make sure the dates are excel dates (numeric) then after applying the pivot right click a date in the pivot table and select group, then group by day AND month -- Regards, Peo Sjoblom "Z" <arthursdomain@gmail.com> wrote in message news...
Date/Text Formatting
Hi, I have a column with in date format and would like to get this into a text format as follows : Column would say : 01/03/04 I want text to say : 'The month is March' Any help would be appreciated... Thanks Tappie One way .. Assuming dates are in col A, A1 down Put in B1: ="The month is "&CHOOSE(MONTH(A1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oc t","Nov","Dec") Copy down -- Rgds Max xl 97 --- Please respond in thread xdeme...
inactive auto
<a href="<=======}==O"><=======}==O</a> ------------------------------------- How can I Automatic item Inactive when it goes to 0 on hand, is there anyway. can anyone help me. Also I need to know how not let the cashiers change the item on hand. tahnk you ##-----------------------------------------------## Newsgroup Access Courtesy http://www.rockryno.com/ Tax and Accounting Software Forums Web and RSS access to your favorite newsgroup - microsoft.public.pos - 39423 messages and counting! ##-----------------------------------------------## ...
auto sort macro
I am trying to use a macro to sort a table of data on worksheet 3, whenever I update a value on worksheet 1 using Excel 2003 (Copy of macro below). Although the macro picks up the change in data on sheet 1 and runs the sort subroutine, nothing happens. I have discovered that by moving the tables to sheet 1 and repointing the macro to the data that it does work and while this is sufficient, I would like to know why the macro does not work when the tables are on sheet 3. Thank you for any advice anyone can provide Private Sub Worksheet_Change(ByVal Target As Range) If Not ...
How to stop Excel from auto updating chart?
Hi, I have written a VBA that will find some averages for me. It'll output the averages in the same range of cells no matter which monthly/seasonal averages I wanted. Is there a way to make the chart so that let's say...after I run the script to calculate the winter averages, then make a chart of it on a worksheet, when I re-run the script for summer, the chart wouldn't update to the summer data? In another word, can I have the chart to not update? I know under there's Tools->Option->Edit->Ask to update automatic links but I don't think this is what I'm loo...
Genrating text and auto number
How to generate auto number but i want it to be combination with text Example L0001, THEN L0002 and so on..how? -- Message posted via http://www.accessmonster.com Emily, What does the 'L' stand for ? Will this field contain other series of numbers that start with some other letter but which must also start sequencing at 00001? By storing more than one piece of information in a single database field, you are violating the first normal form of database design. I would recommend you store the letter designator in a separate field. Assuming that you have other letters, that mu...
Automatic Populate Todays Date in Cell when File is Saved.
I am creating a user data entry spreadsheet in excel and I want to record the date in a specific field when the excel file is opened. I need the field "Submit Date" to be automatically populated and protected when a user opens the file and performs a save. If the user does not Save the file the date field should remain empty. Can anyone help with this please? Paste the following macro into the Workbook module. You access the Workbook module by right-clicking on the Excel icon to the left of the word "File" in the menu across the top of the spreadsheet, select View C...
Date prompt appearing twice due to chart in report
Hi All, I am creating a report based on a query. I have a prompt to ask for the Beginning Date and the Ending Date. When I run the query I get the correct results. I also get the correct results when I run the report. However, I have just added a pie chart in my report footer, and because of this, I am always forced to enter the beginning and ending dates twice when running the report. What do I need to do to make the prompt only appear 1 time for each date? Thank you! decklun The prompt appears each time Access needs to run the query. Once for the report and a second time for ...
Count of Weekdays Between Two Dates
Task: With a given start date and end date, print a list of Months showing a count of weekdays and weekend days in each. It seemed like such a simple request... but how to get started? If I already had a table of all dates between the start and end dates, I'd have it whipped. Any thoughts appreciated. -- croy Here are some ideas you can use for this. First, you need to know how may days are in the month. Then you can use the function below to return the number of work days in the month. The subtract the work days from the number of days, and it will return the week end days. ...
dATES IN BOXES
hi All: THis is just killing me; why is it that when I enter in a number into my spreadsheet the it comes up with month attached. For examply when I enter in the number 4 it populates the field with 4-Jan. Please help this is just a real pain in the asss Mark, Does this happen in every cell or just a few. The formatting of the cell is probably set to a date format. Select the problematic cells, go to the Format menu, choose Cells, then the Number tab, and choose General. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chi...
Starting mileage on 1st day of month, ending mileage on last day of month
Hello! I have a table set up with Date, Vehicle Number, Start Mileage and End Mileage. I want to be able to pull up the start mileage on the 1st of the month and the end mileage on the last day of the month. I have read through other posts and can't seem to put my finger on what I'm looking for. Thank you in advance for the help!! -- Message posted via http://www.accessmonster.com The structure you described (Date, Vehicle, Start, End) implies that any date, vehicle, start and/or end can be entered. What do you mean by "pull up the start mileage on the 1st of the month&...
Auto Calculate Question / HELP!
When I click on the fill handle to drag the data down the cells fill with data grabbed veritcally, for example: When I drag the fill handle down the formulas look like: =D21 =D22 =D23 =D24 =D25 But is there a way for Excel to be adjusted so that they attach the information laterally instead of veritcally? For example, I want to drag the auto fill DOWN but want the cells to read: =D21 =E21 =F21 =G21 =H21 Thank you! Try this... Assume you enter the first formula in cell A1 =INDEX(D$21:H$21,ROWS(A$1:A1)) Drag down as needed. Adjust for the correct end of range ...
email date stamp
hello all Can you tell me whether there is anyway to change the Outlook email stamp to a different format All emails from everyone all seem to have the following format Sent: Wednesday, August 23, 2006 6:10 PM for recording when the email was sent. All emails I send use the same format. A colleague of mine has told me that he can make Outlook stamp all his outgoing emails with the following format: Sent: 2006-08-21 05:28 Can individuals control this? if so how? In your display? View-> Arrange By-> Current View-> Customize Current View-> Format Columns... -- Robert Sparn...
Date expressions
Hello I am just wondering if there is an easy way that an expression can be used to calculate the number of days in a month? Any assistance would be greatly appreciated Thank you in advance AC well, the number of days in a month can change - specifically, the number of days in February, depending on what year it is. for the number of days in a *specific month of a specific year*, the following will work, as Public Function isDaysCount(ByVal dat As Date) As Integer Dim dat2 As Date dat2 = DateAdd("m", 1, dat) isDaysCount = DateDiff("d", DateSerial(Year...
Loses "All day event" option
Quite frequently all my appointments marked with "All day event" will forget this option and become a 2 day event. This happens whenever I change my computer's timezone setting. Is there a way to tell Outlook that I really want an appointment to be an "All day event" regardless of the timezone? I travel and switch timezones frequently. Even the holidays are getting screwed up. Using Outlook 2003 SP2. Thanks. What do you expect to happen when changing your time settings in Windows? It affects every application that uses time zones for its information. Why not use...
Want to avoid pasted text becoming dates
Occasionally, I will paste from a table arrangement on a website. Sometimes, this data will contain text that has some characteristics of a date, eg, 3/16 as a fraction. I could live with it becoming 0.1875, but not March 16, 2005. How can I prevent Excel's intelligence from overcoming my stupidity <g>. TIA, George One work-around is to paste into notepad. Save that file as *.txt Then open that text file in excel. You'll be prompted by the text import wizard for how you want each field. GeorgeB wrote: > > Occasionally, I will paste from a table arrangement on a w...
auto number columns in excel
Is there a way to set up a column of numbers so that it automatically updates the numbers every time a line is added or deleted? >-----Original Message----- >Is there a way to set up a column of numbers so that it automatically updates >the numbers every time a line is added or deleted? >. >Use =count(A1:A10) or more rows as required. If its the last row used - =row(B1) extended down to maybe B100. Then you can use =large(B1:B100) this will give you the last row used. Mal I either did not understand the reply given or was not clear in what I am trying to accom...
Auto Accept not removing cancelled meetings
The Auto Accept agent (running on E2k3 server) is not removing cancelled meetings from my resource calendars. Although the resource will let users book over the cancelled meetings, the calendar still shows the time slot as busy in the attendee availability view. If you go directly to the resource calendar, it will show the meeting as being cancelled. Am I missing a config somewhere that would totally remove the cancelled off the recource? Thanks in advance for any help! If you want a product that works properly, check out http://www.swinc.com/erm. -- Ed Crowley MVP - Exchange &qu...
Archive Modified date
QQ, I have just moved users from one mail server to another. Now, archiving no longer works. I looked at this and found out that archiving works with modified dates and since this was changed recently I am stuck. Do you guys have any idea, if their is a work around for this. Users have lots of sub folders. Thanks B Not really...you can manually archive by dragging and dropping items to the archive folders, or you might be able to use one of the 3rd party solutions listed at http://www.slipstick.com/addins/housekeeping.htm. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to m...
Follow-up: Dynamic 90 Day Running Average
That works beautifly - almost. It will give me the 90 day average for today. Very nice, thank you. Is there a way to generate a 90 day average for each day in the database? PS - when I started writing this "Frankenstein," it was just designed to keep attendance, so the field names weren't that important. (Poor form, I know. But at the time, I had never used Access before [old DBMS programmer of FoxPro and dBase].) I want to change it, and will take the time to do that here soon...or write the whole thing in VB. :D Thanks again. "Ofer Cohen" wrote: > L...
Outlook 2007 & "Enter Network Password" Popup?
Has anyone that has been using OL 2007 experienced the "Enter Network Password" popup windows? I was told by my ISP that it is an Outlook 2002 issue only. Can anyone dispute that? Thanks Oops "<--Mardman-->" <zeus661@yahoo.com> wrote in message news:89adnXUtn8zh0xnYnZ2dnUVZ_sKunZ2d@buckeye-express.com... > Has anyone that has been using OL 2007 experienced the "Enter Network > Password" popup windows? I was told by my ISP that it is an Outlook 2002 > issue only. Can anyone dispute that? Thanks > I'm also seeing th...
Auto Insert Parenthesis
How do I auto-insert parethesis around prepopulated data in a worksheet? I have a column of information that only if there is information, then I would like it to be in parenthesis. For example, A1: data B1: data C1: no data D1: no data E1: data I would like to auto-insert parenthesis around the cells that show "data" but to ignore cells that have "no data". This would involve only 1 column in the excel file. The cells that contain data would look like this, example: (Smith) or (Jones) rather than Smith or Jones. Sub parens() Dim rng1 As Range ...
Date Calculations
I have to create a formula in excel that takes a date (4-1-2010) and subtracts another date from it (2-1-2008) and gives me the remainder of months. Any ideas? How do you define a month difference? 30 days? Calendar months? What about months with different numbers of days. E.g., how many months between 28-Feb and 31-March. 1? 1+3/30? You need to define how the months should be calculated. At its simplest, just subtract one date from the other and divide by 30. That will give one of several possible answers. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel,...