Problem with Date Manipulation
Okay.. Im not a kid, 36, been working with excel for years & last year
did up to intermediate courses in Access.
However, that being said because you guys are very keen in here, so I
dont want to sound stupid, whilst my understanding on alot of excel is
generally on the layman terms!
Heres my problem.
Im making a simple spreadsheet.
I have a widget that enters my facility on one date, it then exits
another. I know by setting up EXIT - ENTER = DAYS I have my duration in
BUT---> I dont want the weekends to count as days!
Material arrives every weekday & exits the..."Configuring Microsoft Office Basic 2007" every time I start
I added memory to my PC and Office 2003 started to misbehave (and it could
not find a CAB file that was on the CD) so I removed it and upgraded to
Office 2007. Now every time I start my PC it spends time configuring
itself. I might add iTunes is the same. It means starting the PC is an
even more laborious process than usual.
Oh, and I have some annoying update to XML something or other that does not
install either, and does not go away.
It is ironic that the extra memory was to improve perforamnce. Every step
forwards involves two steps back.
Apart from swapping to App...Help creating a script in SQL or Calculated field in Crystal
How do I take this script and manipulate it to give me one long string with
static text as well as SQL data in specific positions within the string.
Positions / Data:
1-3 / 173 (Static text)
4-6 / spaces
7-10 / "X_UPR30300"."YEAR1", (has to show up as 2007. Showing up as 2,007)
11 / 4 (Static text)
12-22 / "UPR00100"."SOCSCNUM"
23-57 / "UPR00100"."LASTNAME"
58-92 / "UPR00100"."FRSTNAME"
93 / "UPR00100"."MIDLNAME"
94-120 / "UPR00102"."ADDRESS1"
121-148 / &q...macro for comparing fields in two work books
I have two excel spread sheets (say A1 & A2). A1- is the master sprea
sheet and A2 is smaller spread sheet with very few details.
Suppose Column B in A2 has 100 partnumbers and Column C in A1 has th
superset of partumbers (1000) and corresponding details for each par
number, I need to:
1. Check if all the 100 part numbers in A2 has a corresponding match i
2. Extract the info for the matched partnumbers from A1 and list in
3. Even if there in no match A1, let's say for 40 of them, it shoul
still list those parts in the new sheet.
Can anybody help me with t...UTC time to Central Time (US)
I need to convert UTC time to US Central Time Zone.
The date is in one column and the time is in another below.
A | B
Date | Time
2005-10-24 | 12:15:00
I want to convert the Date and Time columns in UTC
to a single Date and Time column in US Central
Thanks for any advice
Assuming UTC is the same as GMT then the difference is 6 hours STD,
so if the time is 06:00 AM it is 12:00 PM GMT, thus add 6 hours and since
the y are all numbers just add
=A1+B1+(6...Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat
in Excel. Here's my problem:
I have a workbook with 2 worksheets; one is the main data, the other i
the statistics from the main data. Within the main data, there is
column titled "date paid" and another column titled "total paid" (ther
are a bunch more columns, but they don't matter for this problem). Th
"date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O
the stats page, I have columns titled for each month of the year an
would like each to include how much was pa...Time duration formula
I would like to know the formula to work out my employees employment
duration, from a given start date to the current day.
Take a look at DATEDIF. Explained at
(remove nothere from the email address if mailing direct)
"Ska" <Ska@discussions.microsoft.com> wrote in message
> I would like to know the formula to work out my employees employment
> duration, from a given start date to the current day.
> many thanks
I have us...Outlook hangs ,7th time :-(
In our company we are using Exchange 2000 as mail server,
and outlook 2002 sp2 as email client. Each client is
configured to download its emails to a local pst file. We
are facing the following issue :
If you add a rule to move messages from inbox to a
particular folder, during send/receive and just after
receiving 3-5 messages (and these messages are moved to
their folders) the outlook stops responding.
If you run outlook with /safe parameter it will not hang!
If any one could advise I would be gratefull.
...Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns:
ColID - PRIMARY KEY
Col1 - Number
Col2 - Date/Time
Next I created a multi-field index using Col1 & Col2. I entered the
following row in the 1st row:
Next when I tried to add the above row again, as expected, I wasn't
allowed to enter the same row. Next I entered the following 2 rows:
Both the rows were accepted. After this I deleted the 3 records, went
back to the design view & changed the data type of Col2 from Date/Time
to Number keeping the multi-field index...Planning Lead time-Item Vendors Maintenance window
Previously used manufacturing and MRP in v9 and the Planning Lead time on the
Item Vendors Maintenance window (bottom left corner), we used to enter 7 for
the number of days in 1 week. MRP then suggested to place PO's using these 7
days as one week.
Now when we enter 35 for example ( V9 calculated 5 weeks for placing the PO)
Now since V10 it looks like it is calculating 7 weeks lead time (35 days
divided by 5 days)
Did something change with V10 on this field and how MRP uses it with the
Request Resolution window?
The system now looks at calendars and can conside...dynamic field content of parameter combobox
I have to filter my Crystal 9.0 report with a parameter combobox field in
Microsoft CRM. The data for this parameter field has to be loaded out of a
database field dynamically. I have found the possibility to load database
field values in a parameter field but this will not be dynamically. If there
is a change in the database content the parameter field content will not be
updated. I hope to create a combobox control is no problem.
Is there a way to turn this into reality?
Thanks for your help
...Deleting a single character in a text field
In one of my tables i have a field that has a text values like '123.23123' i
would like , to delete the full stop. How can i do this. I cant do it in
excel as the number of records that i have is over 200000.
> In one of my tables i have a field that has a text values like '123.23123' i
> would like , to delete the full stop. How can i do this. I cant do it in
> excel as the number of records that i have is over 200000.
Create a update query, use
Replace([yourField], ".", "")
as new value.
--> stefan <--
I have a couple of excel problems to do with dates (Excel XP/2002).
I have a formula that displays one date minus another and displays the answer as the number of days. Firstly, is it possible to convert the data to display as year and days (375 displays as 1 year and 10 days) as it displays as a decimal point when I divide by 365.
Secondly does anyone know a formula that sorts out leap years (see above).
Any help, suggestions or tutorial links greatly appreciated
As long as you assume 1 year is always 365 days
=INT(A1/365)&" year(s) "&MOD(A1,365)&&q...Can I display the current date in a text box?
I know how to display the current date in a cell, but can I display it in a
text box? And how would I do that?
You would have to have some code to load it, such as
Textbox1.Text = Format(Date,"dd mmm yyyy")
or link the texbox to a cell with the formula =TODAY()
(remove nothere from the email address if mailing direct)
"stephiebrady" <firstname.lastname@example.org> wrote in message
> I know how to display the current date in a cell, but can I display it in
> text box? And ...Validating two fields
I am trying to create a database in which some of the fields should be
mandatory only under certain conditions.
In my "Type of Injury" column, I want the user to be required to enter a
value in this column, only if the value in the "Type of Document" column is
"Nursing Report". Is this possible? I am only just beginning to teach
myself Access, so please bear with me. Thank you.
Put a validation rule on the *table* (not field.)
2nd example under the the Validation Rules for Tables section here:
http://allenbrowne.com/Validati...Adding Fields and Tables to MSCRM
Is there a link or some reference on the ability to add fields to existing
tables in the CRM database and/or adding complete tables to the CRM
database?? I am asking this from the point of view relating to other
existing CRM products (saleslogix, frontrange - shh). From what I have
played with so far from playing with my SBS2k/MSCRM install is I my need to
have a MS developer skillset.
Using the schema manager in the deployment Manager MMC snap-in, you can add
fields to the existing tables. However, there is no way to add new tables.
"Robb D" <robbd@eproductscons...Adding time
I want to add time,like a sum.
I keep getting a basic time like 12:00. Could you please supply a formula.
In the cell with the formula, Format>Cells>Number>Custom [h]:mm
Putting the [ ] brackets around h allows it to sum past 24 hours.
> I want to add time,like a sum.
> I keep getting a basic time like 12:00. Could you please supply a formula.
> Thank you
...Use Datedif but for future dates
I have a formula for identifying years, months, days from a past date to
now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& " M, " &
DATEDIF(C6,NOW(),"md") & " D"
I'd like a formula that can produce the same format (years, months, days)
between now and a future date.
Thanks in advance,
Try this with the date in A2
=IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"Y") & " y " & DATEDIF(TODAY(),A2,"ym") & " m","-"&...Time Entry
A simple problem I would appreciate your help with.
I have a work sheet with several cells where time is to be entered in the
format hh:mm. Unfortunately a lot of users are enetering it in the format
Is there any way I can automatically convert to the right format, or not
accept the (.) but only (:).
Thanks for your help.
Private Sub Worksheet_Change(ByVal Target As Range)
If InStr(1, Target.Text, ":", vbTextCompare) < 1 Then
MsgBox "invalid entry"
Enter the above code in the sheets module
-------------------...Advanced Find should let me search records between two dates
When searching the system for records, many times I need to search for
records that came in between two dates. For instance, I would like to be
able to pull all records input in CRM between March 1 and March 15. In 3.0
you can only query specific dates like "Last X days," "Last X Months," "On,"
"On or After," etc., but you cannot search the date fields between two dates.
The functionality was available in 1.2 but is not available in 3.0.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the...Sort search results by date
It would be nice if i could sort my search results in this forum by date.
I would like to see my most recent questions at the top.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssolutions/Community/NewsGroup...I'm trying to display the next month in a text field
I've gone through all the forms and can't seem to get the code straight for
displaying the next month on a text field on my form.
I just want the entire month name and nothing else and I have been struggling
DateSerial(Year(Date()), Month(Date()) + 1, 1)
but its showing the day and year too and thats not what I need.
Thanks in advance!
>I've gone through all the forms and can't seem to get the code straight for
>displaying the next month on a text field on my form.
>I just want the entire month name and nothing else...Mail (outlook 2002), 'To' field, general question
Why do some names of some messages in the 'To' Field appear with single
quotes in my Sent Box? I'm using Outlook 2002.
I'm sure there's an easy explanation but it stumps me.
...Changing multiple appointment times
I have hundreds of appointments and would like to move
them back one hour. I know there is a link in microsofts
support site that lets you change the time zone without
changing appointment times. However, recurring
appointments are not supported by that method. Does
anyone know of a way to move all my appointments back one
...Changing the names of fields in tables after creating other object
I just leaned about the naming conventions after I have created my tables,
reports and several queries. Can I change the name of fields in my tables
(to remove the spaces and give them unique names ie not just last name but
childlastname) without destroying the work I have done in queries, reports,
Positive Direction for Youth & Families, Inc. (www.pdfyinc.com)
Possibly. If you are using a newer version of Access, say 2003 or 2007 AND
you have Name Autocorrect, and all it's options, enabled, it MIGHT work. I
found it somewhat buggy.