another quetion with my homework.
Use the DSUM function to calculate the total number of vacation days
I put in this: =DSUM('Employee Data'!E6:E109,"17",D4)
You do this for days remaining. I get the E6:E109 from the Employee Data
sheet that Is titled Remaining Vacation Leave. I get the D4 from my leave
summary sheet that shows the total days as 612. You do this for 17 day vac,
12 days of vacation,7 and then 0. I get an error #VALUE Not sure what to do
or where to go with this.
Too old to be in college
It is a pretty good explanation in help for the ...Year to Date Information on paystubs
We are using Canadian Payroll and currently the system only reflects YTD
values for those pay codes that are utilized during a given pay period. Any
pay code that may have been used in prior pay periods is not shown. This is
a real problem as year end is approaching and employees want to be able to
verify T4 values against their last pay stub for the year. I realize that
the US version has the capability to do this and suggest the same be done for
the Canadian version too.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions...Sorting tasks by due date
I want to sort my tasks so that the most pressing items are displayed at the
top of the list, i.e. by ascending due date. I have setup my tasks to sort
by due date in ascending order, but the problem I have is that items with no
due date are displayed above those with a due date.
How do I sort by due date, in ascending order, with items with due dates
displayed above those without?
Thanks in advance
This is the same question that scorpius asked. I believe that there is no
way to do this.
"Jonathan Longthorp" wrote:
> I want to sort my tasks so that the most pres...2007
I was hoping someone could help me please. Here's what I have. A
worksheet that has several dates in a row. D1:T1, Of that range, I
have conditional formatting set to highlight a date(s) either = to or
within the next 7 days.
I need to be able to pull the date next closest or equal to today.
Can anyone help me out?
Any help would be greatly appriecated.
> I was hoping someone could help me please. Here's what I have. A
> worksheet that has several dates in a row. D1:T1, Of that range, I
> have conditional formatting set to highlight a date(s) either = to or...Date Cell Corruption
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC
I have found a "bug" in the new excel which means if I copy and paste a date or date range, from an earlier version of Excel (ie an older spreadsheet), the date in the 2008/new excel changes to a date 4 years and 1 day more than the original date. Even if I try to link the cells between new and old excel, the returning date value is incorrect. Does anyone know how to fix this???? HELP!!!
Sorry to burst your bubble :-) but you haven't discovered a bug. What you've
discovered is that th...if date has Saturday or Sunday in it need to add 2 or 1 to date
I have a speed sheet that calculates the number of days required to get a
in a feild it calculated the estimated completion date based on the new
start date each morning.
but i need it to show Monday if thet date is Saturday or Sunday.
On 22 jun, 15:16, ray <r...@discussions.microsoft.com> wrote:
> I have a speed sheet that calculates the number of days required to get a
> project completed.
> in a feild it calculated the estimated completion date based on the new
> start date each morning.
> but i need it to show Monday if thet date is Saturday or Su...Remove auto date function in cells
When I type '11/12' for example in Excel 2003 it automatically changes it to
'12-Nov'. How can I remove this function? I tried endlessly to change thing
in 'options' but nothing seemed to work!
Thanks for any help
Format the cell for text. (This doesn't fix cells you've already typed
It always will do that as it sees a date,
try '11/12 the apostrophe will show in the formula bar but not in the cell,
the ' converts it to text,
"TKassis" <TKassis@discussions.microsoft.com> wrote in message
news:EF7291DB-615E-4D64...Automatic Date Update
i am in need of a formula that will compare the current date with th
date in another cell. if the date in the other cell is past th
current date, i want that cell to be highlighted in Red. For example
if the date in the cell is June 10, 2005 and today's date is June 22
2005, i want excel to turn that June 10, 2005 cell Red. Also i
possible can i tell excel to turn the cell Yellow if that date HAS NO
passed but is approaching (say within 30 days)? Can someone pleas
tell me 1. if any of this is possible, and 2. what formulas i need t
accomplish this. Thanks
------------------...Problem with dates
I have a date problem. I have got a workbook with a date column from a
When I copy a cell from the date colomn and past it in another workbook I
get a different date.
When I format it in text format, there are the same numbers.
I have a cell with format Date (15-3 05) and the cell value 27-5 05. (The
value for this cell in text format is 37037). I copy this cell and past it
in a New Workbook, and the date is 26-5 01. I look at the format and it�s
the same. And the value in text format is also the same, 37037.
But when I past it in the same workbook I copy it from, th...How convert Date Between Hour 00~07AM?
Hello, I have a query where the completition is like 3/4/10 6:51AM,
the second shift is working from 7:00PM~7:00AM.
So I need to get a day before, in some case where the time is 00:00Am~7:00AM,
please help me on that,
3/4/10 6:51 PM 3/4/2010 OK
3/4/10 6:51 AM 3/3/2010 Need a day before
3/5/10 12:51 AM 3/4/2010 Need a day before
IIf(TimeValue([Completed_Date]) < #07:00:00#, DateAdd("d", -1,
Doug Steele, Microsoft Access MV...Excel: Is there a way to calculate the date as week of month?
I have need to show dates as follows: 1/1/05 = 1st Saturday of January...any
ideas out there?
You can't format the dates as 2nd Sunday of January 2005 but there are ways
of using formulas to get nth day etc
"debra adams" wrote:
> I have need to show dates as follows: 1/1/05 = 1st Saturday of January...any
> ideas out there?
As Peo mentioned, I doubt you can format the dates as
such. You can, however, convert it to a text string in
another cell. Assuming your dates are in column A,
How do I add 1 calendar month to a date, regardless of the number of days in
Maybe this : =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
Change A1 for your needs.
"KRK" <email@example.com> wrote in message
> How do I add 1 calendar month to a date, regardless of the number of days in
> the month?
"KRK" <firstname.lastname@example.org> wrote:
> How do I add 1 calendar mont...DSUM
Hi, can anyone help please ?
running a DSUM fomula below
H1:I2 holds a conditional name for a sales rep to extract his sales from the
data on a specified date.
The problem I have is the Quantity needs multiplying by the it_cost on each
before the DSUM result is obtained.
i.e. total sum of all reps sales on a specified date Qty x cost = x
do I extend the DSUM formula to get the required answer?
...Calculate date based on other table
Below are my 2 tables:-
ItemID (primary key)
Serial number(primary key)
I need the dateExpired to be auto calculated when I enter the ItemID. The
dateExpired is today's date + the days in the table 1...May I know the way?
Message posted via AccessMonster.com
It would be a good idea to create an index for the ItemID field in Table 2
You did not say whether there is a form involved of if you are doing
recordset processing or if this is being done in a query. Where you...Date Format #29
How do I format a date so that when I type 8-4, it gives Aug-04, or when I
type 5-3 it gives May-03. This is month and year. I can use a work-around
and custom format mm/dd which gives me in the printed form what I want, but
when you click in the cell it will shows 8/4/2005 in the formula bar. This
is a month and year format and I want it to reflect nothing more in the
formula bar. If 8-4 is August 2004, I want it to give me 8/04 in the cell,
but something in the formula bar showing that this is month and year, i.e.
8/2004. Hope I have explained myself. If I format mm/yy, then I h...COMPARING DATES
I have a workbook with two sheets.
One of them has some data and the other has a ODBC connection to an Oracle
I use the following array formula to get some values
= INDEX('Cementaciónes Ya Cargadas'!$D$2:$D$76,MATCH(1,('Cementaciónes Ya
Cargadas'!$A$2:$A$76='Cementación Secundaria'!$B8)*('Cementaciónes Ya
Cargadas'!$B$2:$B$76='Cementación Secundaria'!$E8)*('Cementaciónes Ya
The problem resides in that one of the values to compare, s...dsum with 2 criteria
Whats wrong with this code
YearCabinsTurnover = DSum("[SumOfSubTotal]", "Query8", "[Year]=" &
NowYear And "[OrderType]= '2'")
On Wed, 24 Feb 2010 13:45:37 -0800 (PST), Simon wrote:
> Whats wrong with this code
> YearCabinsTurnover = DSum("[SumOfSubTotal]", "Query8", "[Year]=" &
> NowYear And "[OrderType]= '2'")
What is NowYear? A Control name on a form? I'll assume it will have a
Number value, i.e....DSUM search issues
I'm using a simple dsum to collapse and summarize records.
My criteria is a text field. My target or search database column is a text field.
DSUM returns invalid summations, because it appears to find text matches for any target record that has common characters starting from the left most or first cahracter.
ie.. DSUM return values where
Criteria = bob
target = bob value to return = 1
target = bobby value to return = 1
target = bobxxxx value = 1
dsum returns 3.
this is incorrect, as i only want the records for "bob".
i do not want the records for "...Specific Date Headers
I have a report that is sorted by date. I have formatted the dates with a
"ww" format that groups and creates a header by the particular week number.
For every group or week number I would like to create a header that states
the week number and the date of the monday of that week.
Data1 Data2 1/8/08
Data1 Data2 1/9/08
Week 2 Beginning Monday 1/7/08
Data1 Data2 1/8/08
Data1 Data2 1/9/08
Any thoughts, ideas, or suggestions would be appreciated.
If the question is unclear, please let me kno...Handles Dates in 19th Century Wrong
I have been working with an Excel sheet that I need to
enter 19th century dates. When you enter a date as an
example: 3/4/1897, the cell will not format correctly.
When I try to import this data to Access I get errors for
dates that are in the 19th century. Any ideas?
On Sat, 9 Aug 2003 18:50:03 -0700, "Jerry Malone"
>I have been working with an Excel sheet that I need to
>enter 19th century dates. When you enter a date as an
>example: 3/4/1897, the cell will not format correctly.
Excel's date numbering system starts from 1/...using an update query to create records within a date period
I plan our events using Access. At the moment when I'm given the dates for
an event e.g. 1st July to 17th July I have to manually create a new record
for each day.
Is there any way in an update query I could type in my constants (event
type, event name, event location) and the start date and the end date and
create records for all of the days between the start and end date?
If there is, I have no idea what to set the "update to" and "criteria" to?
Any help would be greatly appreciated.
On Jul 26, 11:12 am, James Frater
<JamesFra...@discussions.micros...Get Number of Date between two date but not include Saturday/Sunday
How to using formula to "Number of Date between two date but not
include Saturday/Sunday" ?
2010/12/03 to 2010/12/07 return 3 days.
2010/12/04 to 2010/12/07 return 2 days.
Check out the NETWORKDAYS function in Excel Help. If you are using
Excel 2003 or earlier you will need to instal the Analysis Toolpak.
Hope this helps.
On Dec 13, 9:08=A0am, moonhkt <moon...@gmail.com> wrote:
> Hi All
> How to using formula to "Number of Date between two date but not
> include Saturday/Sunday" ?
> 2010/12/03 to 2010/12/...Excel formula with date constraints
Could use some help with the following:
I created a spreadsheet to track payments made to contractors. In this
spreadsheet I have a simple formula that tells when the payment has to be
made based on the date it was rec'd from the contractor and adding on the
Now what I have to do is add in some date constraints. Our paying office
only processes payments between certain windows during the month. Lets say
that for this month the payment window closes Jan 27th to Feb 2nd. What I
need to do is if after someone enters the date the payment is rec'd and the
14 days is ...Date not coming up!
Hi, I am trying to get results in a query using the between two dates. (ie
between 1/1/2008 and 1/31/2008) The date is formated in date plus time. (ie
1/31/2008 10:27:16 AM) Everytime I try to get the dates for Jan for some
reason 1/31/2008 will not come up in the results. I have 5 fields that need
to show but are not. In the past I have had not problem but since I got my
new computer I can not get the results. Any help is greatly appreciated. I
have Access 2002.
The problem is that 1/31/2008 10:27:16 AM is after 1/31/2008 00:00:00.
All dateTime fields in Access h...Make all dates display 01 as the day
I have a list of dates with various numbers listed as the day (03/25/2009,
04/05/2010, etc.) I need a formula to change all of the dates to display 01
as the day (03/01/2009, 04/01/2010, etc). Thanks!
First date in A1 (03/25/2009)
In B1 =DATE(YEAR(A1),MONTH(A1),1) will display 01/05/2009
Microsoft Excel MVP
"LovingLife28" <LovingLife28@discussions.microsoft.com> wrote in message
> I have a list of dates with various number...