Calculate 1st of month date from existing date.
I want Excel to post a starting date which is 45 days (or other time
period) from an initial date. The rules are: (1) If the date is under
15 (middle of the month) set the starting date to the 1st of the month
as calculated and (2) if the date is 15 or over, set starting date to
the 1st of the next month.
For instance, if my starting date is January 1st, 2006 and I want the
date 45 days from there, the answer is February 15, 2006. Reset the
calculated value of February 15, 2006 to February 1, 2006. If the date
was February 16, 2006 (46 days after start), set the date to March 1,
I should make a condition(AND) in my sumproduct formula with date
format(yyyy/mm/dd), why it dosent accept my condition in one column, like
even I dublicate date column(G) and change the formula to :
but it dosen't work again, any idea to solve this problem would be
I don't see anyting wrong with your formula.
Instead of multipl...Excel 2000
How do I sort dates in a column, as no matter what I try
nothing chnages in either ascending or descending order.
Are you selecting the area where the data is before you try sorting it?
"h" <email@example.com> wrote in message
: How do I sort dates in a column, as no matter what I try
: nothing chnages in either ascending or descending order.
...Query for most recent date
I'm trying to pull the most recent versions of data from a history
table. Each row contains the item being modified, the date the change
was made, what the change was, and which user made it. For each item
being modified I want the most recent change, if I only have the
itemID and DateChanged columns in the query I can do it by enabling
the Total row in the query builder, and set the itemID to groupby and
dateChanged to Max. I also want to show the changeTypeID and UserID
fields for the change identified by the previous two fields, but none
of the options in the Total dropdown appear ...Electronic Date Stamp
Is there a way to auotmatically insert into an E-Mail the
date and/or time it was opened by me?
...Number of months between two dates?
I can't see a formula that calculates the # of months between two dates.
I know that by subtracting the two dates I can get the # of days.
Am I overlooking the formula or does anyone know of an easy way to
calculate the # of months between two dates? I can conceive of a way
that first calculate the number of years, then the number of remaining
months, but that is rather cumbersome, I think, so I'm hoping for an
> =((YEAR(B1-A1)-1900)*12-1)+(MO...Formatting date fields
I could not seem to find the format syntax.
I want to format a date field to display '------' when there is no date
to print in a record.
Can this be done?
If no-one has a better suggestion then you can create a second field in your
query with iif(IsNull(MyDate),Format(MyDate,"dd/mm/yy"),"----"). Show this
field for display purposes and keep the real field for sorting and
"BobC" <Bob.NoSpammm@cox.net> wrote in message
> I could not seem to find the format syntax.
> I want to format ...Sumproduct or??
If I have two columns of numbers:
How do I write a formula that will sum every number in column B that
corresponds to a 1 in column A.
The answer should be 120.
Thanks for any help.
I believe you will want to use a sumif() function here
if your data starts in A1 then
On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote:
> If I have two columns of numbers:
> 1 50
> 2 40
> 1 20
> 4 10
> 3 30
> 1 50
> How do I write a formula that will sum every number in column B that
> correspon...Important Dates
Money 2006. No Passport. XP Home SP2. P4 3.0 GHz. 1G RAM.
I can't get the Important Dates Item to show up on my Home Page. I shows up
on the Customize your Home Page screen, but not on the Home Page itself.
Have removed, saved the file and added it back to the Customize screen.
Have removed all other items and then Home Page says there are no content on
your Home Page. I set up a test date with tomorrow as the selected date,
but it still does appear. Is this a bug or am I missing something?
...If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to
count a range of data for charting.
I am using the following formula:
It works great....except...
In column $M$3:$M$189 there are also
blank cells and I want to count these a...SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri
shall be OR? I.e. something lik
but where you get a count on number of rows where either (both column
are = Car) or (any of the columns are = Car)?
Message posted from http://www.ExcelForum.com
equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...Help Debug Complex Formula (SUMPRODUCT? SUMIF?)
I'm really struggling with this one.
I've got 354 rows of data (rows 3:352). In column G there may be a date. In
column M there is a number (1-12) which represents a monthly period, and in
column O there is a dollar amount which represents a montly invoice total.
I need to construct a formula which calculates the sum of O for a specific
period M where there is a date entered (non-blank cell) in G.
The formula below is what I constructed but it does not work. Rather it
calculates the sum of O for the specified period in M but then multiplies
the sum ($16,200) by the num...How to print in "date received" order
Hi, I'm using Outlook 2003 and I need to print 1000's of emails in
"date received" order.
I can order them fine using the sort arrow, but when I go to print them
they all print out in alphabetical order (I think by "sender").
This is having a huge impact on my job and my helpdesk claim that the
facility to print in date received order is not in Outlook 2003 -
something I find impossible to believe!
Any help would be very gratefully received.
"simonsmith" <firstname.lastname@example.org> wrote in message
news:1124698993.462014.2...Date Subtraction #2
I run Win2K with Excel 2K.
I would like to enter a date in a cell (eg Oct-05) and have the preceding 11
cells automatically put the previous months in.
In cell A15 = Oct-05 (entered)
In cell A14 = Sep-05 (automatically)
In cell A13 = Aug-05 (automatically)
In cell A12 = Jul-05 (automatically)
etc etc etc
Is there a formula that can do this?
Any help will be much appreciated...thanks !
On Wed, 9 Nov 2005 16:34:57 -0800, "John Calder"
>I run Win2K with Excel 2K.
>I would like to ent...PO Integration-date format
I'm using ver 10 sp 4 and I need to integrate PO's in a terminal server
environment. I've used a 'test' user with the date set to the American
format of yyyy/mm/dd the PO will come in just fine. Problem is that we are
in South Africa and our date format is dd/mm/yyyy. The PO won't come in
against that format.
I am using the Excel driver.
Does anyone have an idea how I can bring PO's in without the date format
Please take a look at my article "Supported Date Formats in Integration
Manager"...Transaction dates a day off after daylight savings time
I hope someone can help, I've searched online and cannot find anything.
Since the daylight savings time change, the transactions are downloaded as a
day earlier. For example, in my online banking the date says 4/2, but when it
downloads, it shows 4/1, in which I have to manually change so it can match
my acct. The dates are correct in my online banking acct. I'm using Money
In microsoft.public.money, maxine97 wrote:
>I hope someone can help, I've searched online and cannot find anything.
>Since the daylight savings time change, the transa...Diff between two dates formatted as years and months
I am trying to display an employee's length of service at a particular date
using a simple formula to subtract one date from the other and format the
result as y"y" m"m" so that I get eg. 2y 4m as the person's service.
It is returning odd results eg. 0y 12m for diff between 1/1/04 and 1/1/05
and 1y 1m for diff between 31/12/03 and 1/1/05.
Is there a more accurate method of doing this to ensure that I get the
result I want?
Thanks for all your help
A complete explanation:
Microsoft ...Specific Date from a Range
I have a spreadsheet that has a column of dates that look like this:
...Sumproduct or ?????
I have 2 tables one is cust table and the other one is amount table.
A B C D
Customer Table Amount Table
Parent ID Cust # Cust # Amount
2001 AA AA 5
2001 BB AA 5
2001 CC AA 5
2001 DD AA 5
2001 EE AA -5
2001 FF AA -5
2001 GG BB 3
AA - I want to be able to count if "AA" in Cust table (Column B) then count
positive amount minus negative amount in amount table (Column D). In this
case the answer is "2"
BB - The same thing with "BB". The answer is "1"
...How Can I Convert A (Row, Column) to Range?
For example... how can I do...
Row, Column to some form like A55
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
"kenji4861" <email@example.com> wrote in message
> For example... how can I do...
> Row, Column to some form like A55
Do you mean that you have two numbers representing the row and column
numbers of the reference that you want, for example 1 (co...Adding Dates
I have a field with arrival date then a field with days and another field
with departure date. I enter a date in the arrival date field, then I put in
how many days as a number in the days field and using a Macro command on
exit I want the departure date to show automatically the arrival date plus
How do I do it ?
On Mon, 28 Jan 2008 19:58:48 +0200, Ange Kappas wrote:
> I have a field with arrival date then a field with days and another field
> with departure date. I enter a date in the arrival date field, then I put in
> how many days as a number in the days fiel...Open Spreadsheet--Formatting Changed to Date??
I opened my spreadsheet and some, but not all of the columns on every
sheet in the workbook has changed to a date format. I opened other
spreadsheets and there was no change.
Can anyone explain this. It's a real pain to deal with.
...Range of Cells....
I am currently using a range of cells in a formula...e.g. C15:C20
I want to use multiple ranges of cells....e.g. C15:N15
What I am using is the +SUMPRODUCT formula....
Post your formula(s) for comments.
sumproduct ranges must be the same size
"BenJAMMIN" <BenJAMMIN@discussions.microsoft.com> wrote in message
> I...Increasing the speed of Sumproduct
1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html
In this JEM says that we have double negs so that
can be coerced in to 1. As per JEM"s explanation single unary will coerce
True/False to Zero/One and the second double unary is used so that the
negative values could be converted to its original sign. My "reasoning" was
instead of using double negative sign why not use a single + sign and
achieve further speed increase.
So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The data I used
...Receipt / Invoice printing Terms & Due date
We do a lot of charging on customer accounts. Is there a way to show the
Terms and the Invoice due date on each invoice / receipt?
Thanks in advance,
The only way I have been able to accomplish this is by putting the
terms into a customer custom text field and printing that on the
receipt. We then created a customization that will populate a receipt
variable with the current date + the number of days in the terms. I
have not been able to use any other receipt variable to get this to
New West Technologies
Check out amazing RMS extensions: