# Set Up A Formula That Ignores The 31st Of Each Month?

Jun 18, 2014
I am trying to setup a formula that will add 20 days to date entered in another cell. If the date entered is the 31st (ie 5/31/2014) I need it to return the 21st of the next month. Using the example date - 5/31/2014 - it would return the date as 6/21/2014. I would also need it to return the 30th day if you enter the 11th - ie 6/11/2014 would return 6/30 not 7/1.

View 3 Replies
ADVERTISEMENT
Feb 15, 2014

I have two array formula's, one to count hours, the other to count days. Both are based on: type of worker, day of week, and week number. The problem is if I use a "V" or "T" for vacation or training, then the array formula will not work. Currently I leave the field blank if they are not working, but it would be nice to see whether they are on vacation or training. Is there a way i can have my cake and eat it too?

Counting Days

{=SUM(($E$5:$MN$50>0)*($B$5:$B$50=$D56)*($E$3:$MN$3=F$54)*($E$2:$MN$2=$E$67))}

Counting Hours

{=SUM(($E$5:$MN$50)*($B$5:$B$50=$D56)*($E$3:$MN$3=F$54)*($E$2:$MN$2=$E$67))}

Here is a breakdown of what each of the ranges mean:

$E$5:$MN$50 is the area which the hours are forecasted$B$5:$B$50 is the column for which type of worker (Electrician, Pipefitter, etc)$D56 is the specific type of worker we are counting for$E$3:$MN$3 is a row with all the week days ("Fri, Sat, Sun...)F$54 is the specific day we are counting for$E$2:$MN$2 is a row with all the week numbers$E$67 is the specific week number we are counting for

View 8 Replies
View Related
Sep 10, 2006

I am trying to come up with a STDEVA formula that will only count values greater than 0 in a data set. Is this possible?

View 2 Replies
View Related
Dec 23, 2008

I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:

Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)

- $25K-100K, recognize in two equal parts in months N + 1 and N + 2

- over $100K, recognize in three equal parts over 3 months

N + 1, N + 2, N + 3 ...

View 4 Replies
View Related
Feb 13, 2010

This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.

View 4 Replies
View Related
Mar 20, 2009

I have log data in two columns:

Column A: Date/time (at 30 minute intervals)

Column B: Numeric data

On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.

The end of the range is determined by the month in the current row.

I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.

I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.

Manual

=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536

View 9 Replies
View Related
Nov 12, 2009

I have two, technically unique text values, with leading zeros, countif is including them in the count for each.

D17 = 01234

D18 = 1234

formula in column "C"

=IF(D17<>"",COUNTIF('Part Numbers'!B:B,D17),"")

=IF(D18<>"",COUNTIF('Part Numbers'!B:B,D18),"")

both countif's are resulting in 2, whereas they should be 1 since they are textually unique. The cells themselves, on both the source and counting side are formatted as text.

View 2 Replies
View Related
Dec 22, 2005

I need to average the figures in several cells. However some cells have a 0

in them.

I therefore want the formula to ignore the cells which have a zero.

I have used the AVERAGE & AVERAGEA function, but both count 0 cells.

(although AVERAGEA ignores blank cells, I need to keep the 0s in as they are

linked to another formula)

View 9 Replies
View Related
Jun 29, 2012

I need a count that will ignore font that is strikethrough. Is that possible?

View 8 Replies
View Related
Mar 5, 2007

I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.

=IF((C26-B26)/B26

View 9 Replies
View Related
Nov 28, 2012

Pretty straightforward: randomly and infrequently, Excel ignores my selection of the rear tray paper source on my MX870 Canon. Able to correct issue only by closing out and re-opening Excel.

View 1 Replies
View Related
Oct 16, 2013

range formula that i am using for drop-down lists. One of the drop down lists is based on the range where the data changes(it is a list of jobs that has been released from cad room and when they have been machined a "yes" in one of column appears and then it needs to be filtered so that only non-machined jobs are visible).

Bitmap Part Description

Expected Spindle Hours

Clock In

VLOOKUP

[Code]....

I need a formula that is not taking into account values from hiden rows. OR the formula that is not taking into account values from the rows that have Yes in the 4th column.

View 4 Replies
View Related
Dec 4, 2013

I have a worksheet which has various figures for each day of the week however I need to establish the weekly average of these figures.

Due to the way in which the figures are displayed, I am unsure how to use a formula which does not require a range with cells located adjacent to one another.

I have attached a test sheet as an example. The cells in yellow require the formula and I need a weekly average for criteria 1-3. This formula also needs to be compatible in Excel 2003

Test Sheet.xls

View 3 Replies
View Related
Aug 20, 2013

Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.

The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.

View 1 Replies
View Related
Jan 29, 2010

I'm after a formula this time ... i've searched the board and can't find what i need.

a cell shows 2009 December

and i'd like a formula to covert this to 31st December 2009 .... i.e. for any cell i'd like to know last day of month... and month and year ..

View 9 Replies
View Related
Jan 9, 2009

I am calculating items that refer time service to days...The formula i am using now is

IF (ISBLANK (T2), TODAY (), T2) -IF (ISBLANK (I2), MAX(H2,S2), S2)

However i'm wondering what i can replace TODAY with to obtain a static date such as 12/31/08.

This formula/data is part of a macro that will be run by novice users each month end. So each month I want the measurable date to change. for example on Feb 1 I want the Macro to give me a date of 1/31/08, the following month 2/28/09.

Is there a way to correct the formula? or use a reference table?

View 9 Replies
View Related
Oct 8, 2008

after HOURS of trial and error. I was able to figure this out and get a formula written that would do what I needed. I'm trying to write a macro or formula that will sum the following

View 2 Replies
View Related
Apr 22, 2009

I need some help with formula to display a value based upon a certain date. I have a spreadsheet used within a hospital that records the date of a patients death, the calendar year for the spreadsheet begins April 08 and the year is split quarterly as shown below

April08, May08, June08 = Quarter1 (Q1)

July08, Aug08, Sept08 = Quarter2 (Q2)

Oct08, Nov08, Dec08 = Quarter3 (Q3)

Jan09, Feb09, Mar09 = Quarter4 (Q4)

I want a formula to calculate the value for the "Quarter" column from the patients date of death in the "Date of Death" column eg 02/05/08 = Q1.

Can anyone help me with this?

View 9 Replies
View Related
Jan 21, 2010

In the Total column, I would like to determine what the total would be as from the start date till the current date

Columns "C:I" has the dates and the Monthly applicable rates associated.

(in this example, they are annual dates, but it may be that rates change in between a year as well)

In the first set of details (Mr A), the start date is 01/10/2005

Since Mr A only begins 01/10/2005, the rates from 01/07/2004 - 30/06/2005 ($9) would not apply.

However the rates from 01/07/2005 - 30/06/2006 ($8) would be applicable for Mr A for the period 01/10/2005 - 30/06/2006 (ie.9 months) ....

View 13 Replies
View Related
Feb 11, 2009

i have a report that needs to be filled out with total purchases daily that keeps a running total. So each day i have a column with a new figure. Looks like this:

Date Purcahses Total

30/01/09 10 10

31/01/09 10 20

01/02/09 10 30

02/02/09 10 40

etc

On the 20th of each month, i want to create a macro that wipes out the historical data prior to 1st of that month. i.e. on Feb 20, i want to lose all the january rows.

View 2 Replies
View Related
Jun 19, 2007

I'm making a basic spreadsheet that has to calculate monthly due dates for 'reviews' based on an initial start date.

im using:

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))

.... i know thats probably dead simple to you guys!

which works fine.. except when it comes to times when the start date is for example 31 january, so the sequence goes:

31 jan....... 03 March.... 31 march

and my problem is i need 1 review in each calendar month, so i need to tell that 03 March to be 28 Feb instead.

View 9 Replies
View Related
Jul 6, 2007

is there a formula to list the first and the last day of each month and for every year?.

example:

if i put in cell A1: 01/09/06 ----->01/September/2006

the result should be:

A2: 30/09/06 ------> 30/September/2006

A3: 01/10/06

A4: 31/10/06

A5: 01/11/06

A6: 30/11/06

A7: 01/12/06

A8: 31/12/06

A9: 01/01/07

A10: 31/01/07

A11: 01/02/07

A12: 28/02/07

.

.

.

and so on

how to do that with a formula?

View 9 Replies
View Related
Feb 19, 2008

I have a month number in H2 (1-12). I want a formula that will give me the previous month number. So, if h2 = 1. I need my formula cell to equal 12.

View 9 Replies
View Related
Mar 12, 2008

what is the equivalent command to WEEKNUM if I want to properly calculate Week # of Month?

For example (Sunday being the first day of the week):

January 5th 2008 = Week 1 of January

January 6th 2008 = Week 2 of January

February 2nd 2008 = Week 1 of February

February 3rd 2008 = Week 2 of February

WEEKNUM perfectly calculates this, but it is applicable for the whole year.

View 9 Replies
View Related
Oct 22, 2008

i need to put a date into a month +year

like this:

1/06/2008jun/08

i did this with a format date.

After that i tried to copy/paste special values. So i can make i pivot table on the jun/08. But the problem is he still see it like a date.

MaandTotal

jun/081jun/0810

he sees that 1/6/2008 is not the same as 2/6/2008

that is why i got 2 lines of jun

but i just want one and that he counts all the junes together

View 9 Replies
View Related
Jan 19, 2009

I am trying to create a formula to work out the last working day of each month, but I can't come up with anything that is less than the 1,024 formula limit. I am using a UK calendar so the only two holidays to effect the last working day of the month will be Easter and the last Monday in May.

This is the formula I am using which takes account of Easter, the checking for the last Monday in May is simple enough in another column, but I would rather keep it in one column, just wondering whether there is a simpler way of doing this.

A1 is a specified date and then I would like to work out the last working day of the month, on a new row, for up to the next 50 years....

View 9 Replies
View Related
Jan 27, 2009

Is there any simple formula to get no. of days in a month?

View 9 Replies
View Related
Apr 10, 2007

Is there a formula in excel that will automatically populate a field with the prior months end date? For example - If my spreadsheet is opened on March 25, I would like the date in cell A1 to read 2/28/07.

View 3 Replies
View Related
Nov 21, 2008

When, Cell A1 is blank

1] Formula function : =MONTH(A1)

View 9 Replies
View Related
May 30, 2014

I uploaded an example file.

Now, what I need to accomplish is that the D1 and D3's in sheet 2 need to result in a date next to the correct country (the date (in full) must be the first monday of the correct week). I find it quit difficult to do this because in sheet 2 you have once the country name, but several possible dates. So in sheet 1 there must be a date for every D1 or D3 but under each other.

The second problem is that I need to accomplish to get a "x" in sheet 3 under the correct month where there is an D1 or D3 in sheet 2 (week).

So I need to go from a week to a month and this can be for one country 1, 2, 3 or even more months (it depends from the D1 and D3's in sheet 2).

View 13 Replies
View Related