Tech Problem Aggregator

Nested Formula to stop a counter based on date

Q: Nested Formula to stop a counter based on date

Hi,Hope someone can help! A B C D 1 Business Name Date Reviewed Date Completed Counter What I am trying to accomplish - When a file is added to this spreadsheet the Analyst must add a date. This will be inputted in cell B1, in D1 I have a formula to count the # of days aged (=IF(ISBLANK(B1),"",TODAY()-B1) here's the spin...I need to add a condition that will stop the counter based on the date added in C1 (Date completed)?For example, if I add a file to the spreadsheet today 9/29/2016 and the completion date is 10/5/2016 I should see that the file aged (Counter column) 6 days.Possible?message edited by HarrisLyfe2016

More replies
Answer Match 128.1%

Hi,Hope someone can help! A B C D 1 Business Name Date Reviewed Date Completed Counter What I am trying to accomplish - When a file is added to this spreadsheet the Analyst must add a date. This will be inputted in cell B1, in D1 I have a formula to count the # of days aged (=IF(ISBLANK(B1),"",TODAY()-B1) here's the spin...I need to add a condition that will stop the counter based on the date added in C1 (Date completed)?For example, if I add a file to the spreadsheet today 9/29/2016 and the completion date is 10/5/2016 I should see that the file aged (Counter column) 6 days.Possible?message edited by HarrisLyfe2016

More replies
Answer Match 84%

Our employees can sell such things as vacation time, for example, 1 day of vacation pay but their request must be submitted by a certain time if they are to be paid. We can tell them what day they will be paid based on the date they submit their request. This can be confusing for a supervisor to explain and I wanted to automate it on an Excel sheet.
1. Employees receive their regular check every Friday as well as a check for any time sold.
2. As long as they get it into us within 7 days prior to Friday they will receive their check the next Friday.
3. If it’s greater than 7 days then it would be the Friday after next before they received the sold time check.
4. For example If an employee requested on May, 12th 13th, 14th, 15th, 16th, or 17th to sell vacation time that check would be received by the next Friday i.e. May 25th. However, if they turned it in on May 18th. 19th. 20th, 21st, 22nd., 23nd, or 24th. they would receive it on June 1st.
5. This pattern continues throughout the year. Note that weekends are included.
6. I think the attached will better explain what I am trying to do.

A:Formula in Excel for a Date to Remain Constant Based on a Range of Dates

I think there is something wrong in the way you explain it but, could be my interpretation though
I adden a column using the Days360() function which shows the nr of days between the request date and the 'friday' date, maybe you can use that resulty to compute the new value.

2 more replies
Answer Match 72.24%

I'm modifying an existing Excel worksheet and need help with a formula. I need a condition included in the formula which is IF($S3=0,$I3). All of the other conditions need to remain too. I can't figure out how to add it to the current formula without getting an error message.

Here's the current formula:


Thanks for any help.

A:Excel Nested IF Formula

10 more replies
Answer Match 72.24%

print 25

else if(ratio>20&ratio<40&loss<2000&loss>1000&balance<100
print 20

else if(ratio>20&loss<2000&loss>500&balance<100
print 20

else if(ratio>20&ratio<40&loss<2000&loss>500&balance<100
print 15

else if(ratio>20&loss<500&balance<100
print 15


A:Excel nested (if-and) formula

you are missing a false statement
=IF(AND(BA2<20,AU2<-2000,L2<100),"25",IF(AND(BA2>20,BA2<40,AU2>-2000,AU2<-1000,L2<100),"20",IF(AND(BA2>20,AU2>-2000,AU2<-500,L2<100),"20",IF(AND(BA2>20,BA2<40,AU2>-2000,AU2<-500,L2<100),"15",IF(AND(BA2>20,AU2>-500,L2<100),"15","does not meet criteria")))))

also you may be able to combine two of the IFs into one OR statement where the result is 15

but before that - is it working for you

if not please load a dummy spreadsheet with examples here

2 more replies
Answer Match 72.24%

My brain is gone today. I am trying to create a formula that will pull information from several columns. Here is what I have

Angie, Smith, 758413898

This is what I need as13898 (it must be the lower case letters).

I've tried everything I can think of but I keep getting an error. Can anyone help?

A:Excel nested formula

6 more replies
Answer Match 72.24%

Good afternoon!!I have the following formula on a spreadsheet I am working on =IF(ISBLANK(B10),"",IF(B10>=0,E10/D10,0))Unfortunately, when the result is 0 Excel displays a #DIV/0 error. I am aware of the use of the IFERROR formula however, in the above formula I do not exactly where I would place the IFERROR function name? Plesae help.Thank you all!

More replies
Answer Match 71.4%

=IF(OR(O4>=1,O4*30>=100),O4*30)I need this formula to state IF O4 is greater than or equal to 1, then 04 * 30. IF O4 is blank then leave blank (AND/OR) not sure which one.... IF the answer to O4*30 is 100 or more then default to 100.Please help.

A:Nested IF(AND/OR) Formula & how to arrange the conditions

Try the following formula.When using Nested IF's, you have to ensure that one clause doesn't override all the others. In this case, if the O4>=1 clause was before O4*30>=100, the answer would always be O4*30 because O4*30 will always be greater than 1 for any whole number=IF(O4="","",IF(O4*30>=100,100,IF(O4>=1,O4*30)))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Answer Match 70.56%

What is the best way to workaround the 7 nested IF statments in a formula within Excel?

I have a formula that needs 8 IF statements.

A:Solved: Excel Formula -> Nested IF Statements

16 more replies
Answer Match 70.56%

I have a spreadsheet with listed part numbers that i want to be able to categorise based on the part number containing specific text. I can make this work individually with


so part no abc-BG-123 returns Building


part number abc-CE-123 returns central

But when i try and nest the next criteria I still get Building when part no abc-BG-123 but i get a #VALUE! error when the first criteria doesn't match even if the second one does


Can anyone help with an alternative?

I am in Excel 2010

A:Solved: how can i get nested search formula to work


2 more replies
Answer Match 68.04%

I am having issues of completing this Nested IF statement: My current statement takes care of the issue/or concern if the column is null, however if it's not null and has the incorrect date format. How do I do a date format within the column/or replace?

Following is what I currently have:

Const_Date: IIf(IsNull([Construction Date]),IIf(IsNull([EOL Evaluation sheet.Install date (DD-MM-YY)]),[Building Built.Install date (DD-MM-YY)],[EOL Evaluation sheet.Install date (DD-MM-YY)]),[Construction Date])

And Following is a snapshot of the columns within the statment:

A:Nested IF Statements with Date Format Issues

Hi welcome to the forum,
It's good use to also mention the name and version of the program you're using.
It's probably Access but ...
And "Following is a snapshot ..." well, still looking for it it's not there

3 more replies
Answer Match 67.2%

I need to return a number associated with a date based on today's date. For example: A1:A24 are numbers 1 to 24 and B1:B24 contain the 15th and 30th for each month of the year; 15-Jan, 30-Jan, 15-Feb, 28-Feb, ... 30-Dec.Cell C1 needs to return the number from A1:A24 based upon today's date compared to the date in B1:B24.For example today is 08/21/15, what number should be returned in Cell C1. It should be 15 as 08/21 is less than 30-Aug.message edited by jmichels

A:Return a number based on today's date compared another date.

Try this formula, see how it work for you:=INDEX(A1:B24,MATCH(TODAY(),B1:B24,1),1)This will work for most dates, but errors out if the Date is 01/14/15 or less.MIKE edited by mmcconaghy

4 more replies
Answer Match 59.64%

Hi all,

I have a spreadsheet that lists contacts and has a "Need to Contact" column based on whether the last contact is more than 90 days ago from today. All that shows in the column is Yes or No; however each cell actually contains just a formula.
What I would like to do is sort the sheet automatically (upon opening) so that those persons with the oldest contact dates are at the top. The persons using it don't have a lot of Excel savvy....(not that I really have much either)!

All help is much appreciated!

A:Sorting in Excel based on response to formula

16 more replies
Answer Match 59.22%


Is it possible to hide the entire row if the formula result is "x". (in Excel)

In my spreadsheet, I have vast numbers of results that show up as N/A. This is the correct answer, but it's making the spreadsheet hard to read because of how many there are. It's easy enough to sort afterwards, but I was hoping a a more elegant solution existed?

Thanks for any help!

A:Solved: Hiding rows based on formula result

13 more replies
Answer Match 59.22%

Hello, I am working on a spreadsheet to help employees quickly calculate tank volumes. In short, we keep track of tank volume by height in 16ths of an inch. I have 10 different worksheets containing the height and volume data of the 10 tanks. The sheets are named by tank number. So for example, tank 2301 is in worksheet "2301". In another reference sheet I plan to hide, In B column have the heights in 16ths of an inch. In C column I have the volume associated with that height. I do have a Vlookup formula that works right now but I would like to use one master sheet and need to be able to enter different tank numbers in a selected cell and have the formula reference the data sheet based on the tank number we enter. Each tanks volumes are different which is why this is needed. This is the formula I currently use and it works! Just not sure how to alter it so I can have a cells value determine the reference worksheet in the vlookup formula.


Id like the 2301 value to change based on what tank number we type in which will also be the data sheets name to be referenced. Maybe adding a drop down selection would even be a better idea than typing the number in.

Can this be done?

Thanks in advance for any info!


A:Excel Vlookup formula based on cell data

Welcome to the forum

Hmmm that's a tricky one. So you want the table array to be dependent on the data you type in? I think you can use the INDIRECT function for that (with the function pointing at the data input cell), but you would need to give defined names to all the relevant ranges. For example, you could name the range '2301'!$B$2:$C$519 to something like Tank_2301.

You could use Data Validation to create a drop down list to select the right tank.

How does that sound?

8 more replies
Answer Match 59.22%

Lets say I had in cell $A$1 the number "36", in row2 figures for monthly spend, in row 3 cumulative spend and in row 4 the month number(1,2,3,4..). What I want to do is substract the monthly spend from $A$1 months ago from my cululative figure in row 3 so I have a rolling cummulative hardware spend value of 36 months in this case. So in this case if I was to do it manually in cell AK3 I would have "=AJ3+AK2-A2" and drag it further right. So I add my monthly figure of spend each month to the cummulative figure in row three but also deduct the monthly spend from 3 years prior.

So in order to use the figure in $A$1 I could do something in row 3 like: starting from B3: "=if(B4>$A$1,A3+B2-[$A$1rows_further_back_thanB2],A3+B2). So when I get to month 37 in row 4 I deduct the figure in row 2 (in the formula in row 3 - the cummulative row) from 36 months prior. I hope this all makes sense. Can you help me please (see attached spreadsheet).

More replies
Answer Match 58.38%

I have a start date and end date cell (C5 and C6) I am using a +DATEDIF formula to calculate the number of days between two dates (E8) and the formula is working without any problems......

How can I have exel show me a blank cell if the start and end date cells are empty instead of getting a #NUM!

Any help would be greatly appreciated
Thank you

A:formula for date

my first thought is
try ISERROR in an if

2 more replies
Answer Match 58.38%

Hi ,I want a if formula for the belowIf a date is less than 2 weeks from today then it should reflect as BUCKET CIf a date is more than 2 weeks from today and less than 1 month then it should reflect BUCKET DIf a date is more than 1 month and less than 3 months then it should reflect as BUCKET E

A:Hi , I want a if formula for the below If a date is less t

re: "Be aware that the =TODAY() function is termed volatile, every day your formulawill evaluate the formula with TODAY's date,..."To be a bit more precise, the issue with TODAY() being volatile is not the daily change but the fact that the TODAY() function will calculate every single time the sheet calculates, even if the precedent data does not change. Too many volatile functions can cause significant performance degradation in a workbook. message edited by DerbyDad03

9 more replies
Answer Match 58.38%

In the military we offer our members what is known as Seperation Expense (SE) - When a member is called away on duty or course and is seperated from their family, they receive an allowance (S/E) of $11.50 per day.

Here's where the formula would vary - To make it equal across the board sort of speak - Every month, no matter how many days is treated as a 30 day month. So the max amount a member can claim for monthly S/E is $345.00.

Here are a few good examples :

Case #1: Member starts his course and is entitled to S/E effective the 10th of January (which has 31 days) and does not take any leave, is he entitled to 21 or 22 days? 22 only at the start that we include all days.

Case #2: Member comes in to pick up his S/E for Feb (which has 28 days) and he has taken 5 days leave for which he was reunited with his family. Should he receive 25 days (30 days less 5 days leave) or 23 days (28 Days less 5 days leave) of S/E? 25

Case #3: Member comes in to pick up his S/E for December (which has 31 days) and was reunited with his family from the 21st of Dec till sometime in January. The member is entitled to S/E from 1- 20 Dec, is the formula to calculate his entitlement 30 days less 11 days leave for Dec for an entitlement of 19 days of S/E, or is the member simply entitled to 20 days of S/E for the period of 1 - 20 Dec? member gets 20 days
Case #4: Member comes in to pick up his S/E for Mar (which has 31 days) and tells you that he was on TD (nowhere near his family) and rece... Read more

A:Date Formula

10 more replies
Answer Match 58.38%

Hello all, is there a date/time formula or a macro i can setup. So that when a certain date hits, excel will automatically substract a amount from a certain cell?

For exmaple, if a customer buys a certain amount of supplies, on the same day of every month, lets say the 15th. In the spreadsheet when that date hits, can it automatically debit their account, rather than me doing it manually everytime?


A:is there a date formula?

13 more replies
Answer Match 58.38%

if cell A1 CONTAINS THE DATE 12 JUL 02



A:Date Formula

Try the following formula in cell C1



1 more replies
Answer Match 58.38%


I have an Excel 2003 pivot table based on an ODBC query linked to a Sage 200 (accounting software) database which works fine, but I had to add two formulated columns to the data returned by the query because the logic in them is too complex for me to replicate in the query design editor.

When I refresh the query the formulae in the two columns are not automatically copied down past the row which represented the end of file when it was last saved and so have to be copied down manually and I then also have to go into the Pivot Table Wizard to expand the data range.

Although my VBA is (very) rusty I could probably write a few lines of code to automate these two steps but is there a way to do it without VBA?



A:Solved: Excel Pivot Table (based on query with formula)

16 more replies
Answer Match 58.38%

I am having data structure as below. I need some macro or some means to populate formula in Column D based on column C (as and when it changes) . All other column data are populated. This is beyond me. Any help or direction is greatly appreciated. Thanks in advance

Col | A | B | C | D | E
Row | 1 | Test1 | | | 10
| 2 | Test2 | =A1 | =MAX(E2) }| 15
| 3 | Test3 | =A2 | =MAX(E3) | 20
| 4 | Test3 | =A1&","&A3| =MAX (E1,E3)| 30

A:Excel macro or formula - change field based on other cell

7 more replies
Answer Match 58.38%

I have a sales report that shows number of leads by sales person and date. It's organized into a monthly calendar view (with dates running down instead of across) and I've used formulas so that I only need to change the year to autopopulate the dates to fall within the corresponding month. What I want to do is only show the actual business days in each month - so auto hide any Sunday or Saturday rows as well as the blank rows (just the rows that have formulas in column A, not the blank spacer rows) so that when I update for each year I don't have to manually hide the rows with no data. Can I do this with VBA code? I'm not sure if I've explained myself well, so please let me know if this doesn't make sense! I've attached my spreadsheet.

For Windows XP, Excel 2010


A:Solved: Auto hide rows based on formula results

7 more replies
Answer Match 57.96%

I need a formula that will calculate the following

A member goes on a tasking for a complete 24hr period will receive a payment of $16.41. For everday after that he/she is gone and has worked atleast 6 hrs in that day he/she will receive and additional $16.41.

The payment ($16.41) does not start until the first 24 hour is completed and then every additional day after that (6 hrs or more worked) is another $16.41.

I have it set up in this way :
(E18) (F18) (G18) (H18)
20 SEP 04 08:00 21 SEP 04 14:55

(I18) TOTAL HOURS - =+IF(H18>0,((G18+H18)-(E18+F18))*24," ")
(J18) TOTAL DAYS - =+IF(H18>0,FLOOR((G18+H18)-(E18+F18),1)," ")
(K18) TOTAL ENTITLEMENT - =+IF(J18>0,J18*'FOA Calc'!$C$8,0)

The problem is that for TOTAL HOURS I am getting an answer of 31 (which is correct) but for total days it should be 2 not 1

his first 24hr period is his first day and his 7 hrs remaining entitle him to another day.

can you PLEASE help me with the formula.....

Thank you


A:formula for date & time

7 more replies
Answer Match 57.96%

A person retires at the age of 58 years. If his date of birth is on any day between 2nd and month end.
Ex: if DOB is 15-10-2000 date of retirement will be 31-10-2058

This formula works OK for above

But if the date of birth is on 1st day of the month his retirement will be last day of previous month.
01 Oct 2010 his retirement date will be 30 Nov 2058
01 Jan 1996 his retirement date will be 31 Dec 2033

I want both of them together using if or any other mode of formula

Pl. help me with suitable formula

A:Solved: Help in date formula

10 more replies
Answer Match 57.96%

Using OOCalc 3.1.1:

Was looking for a formula to show a future (monthly) date for an event to take place and found the following:

cell A1: =DATE(YEAR(A4);MONTH(A4)+1*(DAY(A4)>=1);1)
cell A4: =TODAY()

where, when the 1st of the month happens, A1's date would increment to the next month.

For example: On 03-31-10 (cell A4), 04-01-10 would be showing in (cell A1). When A4 goes to 04-01-10,
A1 goes to 05-01-10 ( MM/DD/YY format)

In trying to figure out how the formula worked, I tried the Function Help files but that didn't provide any useful
info. What I don't understand about the formula in A1 is the * in front of *(DAY(A4)>=1);1) . Normally, the
* is used as a multiplication operator; however, it doesn't make sense (to me) in this situation.

What I've been trying to do is modify the DATE formula to do basically the same thing but incrementing
either weekly or bi-weekly ( 04-01-10 to 04-08-10 weekly; 04-01-10 to 04-15-10 bi-weekly). Just don't know
enough .

Was also wanting a yearly incrementation and after making untold number of modifications, finally got it
to work. =DATE(YEAR(A4)+1;MONTH(A4);DAY(A4)>=1) . But the *(DAY(A4)>=1);1) got modified to
DAY(A4)>=1) with the *( getting deleted as well as the ;1 .

Can anyone enlighten me as to how/what purpose the * serves , as well as the ;1 in the original
formula ??


A:Formula for future date

You can try this, it displays the first of the next month


When you see the * in your formula it can also be used as an "AND" condition, a PLUS + would be an "OR" condition but it depends how the formula is constructed as to whether it will calculate or interpret


This does not work..

=IF(B1=1 * B4=3,"True","False")

put brackets around...


2 more replies
Answer Match 57.96%

Is it possible to use the date formula, =TODAY(), in the custom header and footer option. I cant get it to work but thought maybe there was a different way, TIA

A:Date formula in header?

8 more replies
Answer Match 57.96%

I’m doing a mail merge and a date that I would like to merge to my letters is found in 2 different columns on my .csv data sheet. Column A has mm/dd and shows “1106” for November 6th and Column B has yy and shows “7” for 2007. The data is formatted as a number – when I format the “1106” to a date it shows “1/10”. Is there a formula to take the data from both cells and format it to show “11/06/2007”? I'm using Excel 2000 and XP.

A:Solved: Date Formula

16 more replies
Answer Match 57.96%

Need help setting up a date formula in Excel '97.

Have first date of today, have second date of next available appointment. Need Excel to formuate the turn-around time (i.e. today 4/6/04, next appt date 5/5/04 = 29 days.

How do I do this??? It's driving me crazy. It's probably something quite simply that I'm not think of!


[email protected]

A:Excel 97 Date Formula Help!

This is what you are looking for. (I think)

2 more replies
Answer Match 57.96%

I have an excel spreadsheet with rows of service event start and end dates, grouped by id number:
2008/01/05 2008/01/06
2008/01/12 2008/01 14


2008/02/01 2008/02/10
2008/02/02 2008/02/02
2008/02/03 2008/02/03
2008/02/11 2008/02/14
I need to calculate the number of days between the end date and the next service start date - example 1 above is straightforward as it's A3-B1: 2008/01/12 - 2008/01/06

Example 2 gets trickier because the first service event spans 9 days, and other service events occur within that period - I need to find the difference between the last event date (2008/02/10) and the next one after that - (2008/02/11). In my 5 full worksheets of data, the number of rows between the valid entries is not constant - it could be the third row or the thirteenth. Does anybody have any ideas? many thanks!!!!

A:Excel date formula

6 more replies
Answer Match 57.96%

Good day everyone.
Basically I need to have a formula in excel that includes auto hide and unhide of rows based on the result value. Attached is my sample exercise for quick reference. In this exercise, I want to hide automatically the rows under "REPORT OUTPUT" that contains "0" ZERO value. Basic guide: Once you enter value (from 1 to 5) in cell D3, report output will automatically calculate...... The missing condition in the formula is to automatically hide ZERO value.... Please help.
Thanks in advance.

A:Solved: Formula with auto hide and unhide rows based on result value

16 more replies
Answer Match 57.54%

I have a nested "IF" statement that inlcudes the "OR" function because I have more than 7 logical tests. One of the logical tests looks for two fields to be true in order to return the "value if true". If either or both fields are false, then the "value if false" is returned, in this case zero. When the value of the first field is correct, but the value of the second field is not correct, zero is returned, which is the "value if false" and a correct return.

However, when both fields are false, the formula is returning the word "FALSE" instead of zero.

The attached file shows these returns and the formulas. Cells F2-7 are corrrect returns (highlighted in green), while cells F8-11 are not correct returns (highlighted in red).

Any ideas on how to return the "value if false" (zero) in cells F8-11 would be appreciated.

A:Solved: Excel 2007 - Nested "IF" formula using "OR" Function

8 more replies
Answer Match 57.54%

I am trying to create a code that will highlight an entire row the day before the date entered into a cell on that row.

A:Excel/Highlight Row Based on Date

use conditional format
add a formula

=cell with the date = today()+1
that will be true if the date

just to make sure we understand

today() is 7th August
the date in the cell is 8th august
so you want it highlighted

so cell = 8th
today + 1 = 8th
it highlights

now to highlight the row
use a $ on the cell that has the date and apply to the whole row

so if the date is in column C and say row 5

$C5 = today()+1

in the conditional format formula and apply to
or whatever column you need

a sample spreadsheet would be useful , you can attach here

1 more replies
Answer Match 57.54%

Daily we send many mails. We handle the reactions of customers which send us a mail using our subject the first 7 days (not a reply). After 7 days we would like to send an autoreply and archive the mail without looking at it.
How can we do that?
As we can adapt the subject to use, first idea was to add the limit-date and set a rule. But I don't find how to set a rule that compares the day of reception and this date included in the subject?

More replies
Answer Match 57.54%

I have added some bookmarks earlier (have already many bookmarks) but I just can't find them and was wondering if it Is it possible to search for the bookmarks based on the date it has been added to the browser?
Just like opening windows explorer and you can search base on dates, file size or the name.
thanks in advance

A:Possible to search bookmarks based on date?

You did not say which browser but for firefox go to Bookmarks > Organize Bookmards.  In the Bookmarks Window select Views > Show Columns. Check Added for the date added.
Edit: I don't think you can search by date though. You can only sort by date by clicking on the Date Added heading.

5 more replies
Answer Match 57.12%

I am trying to combine information from a couple cells to create a date header for a column in a worksheet.

I want the cell to read:

Date: Thursday October, 21 2003

I can get the first two parts but the date comes back as a number:

Date: Thursday 37917

Here is the formula:

= "Date: " &'[WKH Daily Roster.xls]Sheet1'!$C$1& '[WKH Daily Roster.xls]Sheet1'!$D$1

The last part is the date "$d1$1"

I know this is probably simple but I searched help in Excel and on Micros.. site with no luck.


A:Format date within Excel Formula

Something along these lines?

= "Date: " &[Book1]Sheet1!$A$1&" "&TEXT([Book1]Sheet1!$A$2,"mmmm, dd yyyy")


3 more replies
Answer Match 57.12%

I am trying to create a formula in that:

Col A will be a date of birth typed in as normal(13/06/1967)

Col B will be a formula calculating age on todays date with todays date changing automatically on a daily basis

(All I need displayed is the age of that person today everyday)

Doesnt have to be two columns because I dont need to show the date of birth.

Hope someone can help me, Thank you.

A:Solved: Excell formula for date and age

7 more replies
Answer Match 57.12%

Ok, I need a formula in Excel that converts a date field (i.e., 7/29/04) to the name of the day that represents (i.e., Thursday). I don't care what form the day is, it can be text or date displayed with just the day.

A:Need a formula to convert a date to text

Actually you don't need a formula.

Just format the cell as Custom, with a format of dddd.

That will display only the full day (Thursday), without "destroying" the underlying date data.
ddd format displays the shortened version (Thu).

3 more replies
Answer Match 57.12%

A2 = 03/21/2016
B2 = 04/05/2016

There are 15 days in the pay cycle.

I need to add 5 days to the end date to get the payroll date. But if that date falls on Saturday or Sunday, I need to get Friday's date.

I'm doing the formula in C2, though it doesn't matter.


A2 = 03/21/2016
B2 = 04/05/2016
C2 = 04/08/2016 (because the 10th is Sunday)

A2 = 04/21/2016
B2 = 05/05/2016
C2 = 05/10/2016 (because it's a Tuesday)

I've been trying to work out the WEEKDAY function, but can't get it to work. It's been a while.

More replies
Answer Match 57.12%

I have a small spreadsheet which I use to track my vacation and sicks days. Currently I use conditional formatting to hide each weeks data until the end of the pay period. The one thing I haven't been able to figure, though, is how to prevent a =SUM formula from executing until a specific date is reached. I'd like to end my vacation days early, but when I do the formula kicks in and immediately adjust my totals, rather than waiting until the pay period is over.
EX: If I enter any values in columns C:M, the formula in column Q immediately deducts the amount from Q30. Thanks for any assistance you can provide.

A:Execute formula on specific date

Your sheet pretty does what you says for Q30.
Btw, what is your excel version?

2 more replies
Answer Match 57.12%

Re: Excel formula1. B2 contains date 10/1/20152. B3 needs to contain B2 + 5 WORKDAYS 10/07/2015When I use the WORKDAY function, it calculates correctly However, I want B3 to stay blank if B2 is blank.right now it displays this when B2 is blank: 1/5/1900Can anyone help me with this, pretty please?message edited by lizzyb1120

A:need to formula to automate date +5 days

=IF(B2 = "", "", your current formula goes here)Caveat: I'm assuming that by "if B2 is blank" you mean an empty cell.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

2 more replies
Answer Match 57.12%


I have the formula below which counts the frequency at which the word "absent" occurs within the range B2:B366. If it occurs greater than or equal to 28 it displays a 1.


Is there anyway to adapt the formula to count the frequency within a 12 week period (keeping the same cell range) whereby the first occurrence triggers the count of the 12 week period?

Any help would be appreciated!


A:Frequency Formula within a date range

timmycl_7 said:


I have the formula below which counts the frequency at which the word "absent" occurs within the range B2:B366. If it occurs greater than or equal to 28 it displays a 1.

=SUM(IF(FREQUENCY(IF(Sheet1!B2:B366="Absent",ROW(Sheet1!B2:B366)),IF(Sheet1!B2:B366="PRESENT",ROW(Sheet1!B2:B366)))>=28,1))Click to expand...

Can you break that down in terms of why it's necessary to have anything more complicated than:



2 more replies
Answer Match 57.12%

Dear Tech Guy/s
I work in veterinary practice.
I want to apply 6-monthly price increases across-the-board based on the annual inflation rate for the preceding 12 months.

Can you help with the formula I need to construct?

i.e. where

C = current price
N = new price at 6 months = current price + percentage increase required
I = Known Inflation figure for the preceding 12 months (percentage)



A:Formula for applying 6-monthly price increases based on annual inflation rates

if you just want to increase by the inflation percentage then

depending on how the percent is formatted
say inflation was 2.3%
is the cell 2.3 formatted as a percent
or 0.023
or just as 2.3 in a cell
you need it to be a % so 0.023
and therefore may need to divide by 100
N = C * (1+ I )

N = C* (1 + ( I/100 ))

in the example N = C * 1.023

1 more replies
Answer Match 57.12%

Hi all,

I am trying to develop a cost calculator based on several drop down lists. I have used the following formula:

=AVERAGEIFS('Scope Of Work'!I:I,'Scope Of Work'!C:C,C6,'Scope Of Work'!F:F,'Cost Calculation'!D6,'Scope Of Work'!G:G,'Cost Calculation'!E6)

Cells C6, D6 and E6 in the Cost Calculation Tab are all drop down menus based on data validation. The formula works when all 3 cells have a selection value but I also want it to calculate if only one or two of the drop down lists have been populated, any ideas?



A:Solved: Excel 2007 - Dynamic formula based on Data Validation List

11 more replies
Answer Match 57.12%

Hi there
I want to make a query that shows me the available rooms for a hypothetical hotel booking system.

I want to specify a date range for my booking (eg 15 Aug 2005 to 29 Aug 2005 - 14 days). I want the query to show me anything that falls outside that range and anything without a booking at all. However, I don't want to see any rooms that have any of that date range already booked (eg a room booked 13 Aug 2005 to 21 Aug 2005 should not show up).

I have the query mostly working, except for some reason it won't show up a room that has a booking prior to the date range I specified.

I have a field for the room number from a room information table, and one each for arrival and departure date from a booking table.

Can anyone point me in the right direction?


A:Exclude records based on date range (help!)

Hello, to help us can you post a zipped copy of the database on here, or at least post the Query criteia that you are using that doesn't quite work.

2 more replies
Answer Match 57.12%


I have Dates in column A and values in B.
For some days(dates) i have 3 values, for some days i have 2 values and for others i have one value. (Example of my data given below)
1-Jan-2000 100
1-Jan-2000 150
1-Jan-2000 200
2-Jan-2000 400
2-Jan-2000 600
3-Jan-2000 150
6-Jan-2000 600
6-Jan-2000 650
6-Jan-2000 700
Now I want to Average the values on basis of date (regardless of the number of values/day) (required output should be as under)
1-Jan-2000 150
2-Jan-2000 500
3-Jan-2000 150
6-Jan-2000 650

Any idea how to do that?

A:Solved: Averaging values based on date

6 more replies
Answer Match 57.12%

Can this be done via VBA Module, and I can just create a button that will perform this?

Format is Date_Name
example: 02.15.12_Ricky

I want to press a button and move all of the folders from
C:\Documents and Settings\
where the date is 5 or more days old, and move (or maybe I should say cut) those folders to
C:\Documents and Settings\Archive

A:Solved: VBA to move a folder based on Date

16 more replies
Answer Match 57.12%

I have created a spreadsheet to register equipment and record last inspection date. This date is a constant as all inspections are carried out at the same time. I then applied a simple formula to calculate the next due date, this is either 12 months; 6 months or 3 months.
14 days prior to the next due inspection the cell adjacent to the Next Due Date becomes Red and reads "TEST"
I need this event to trigger an email to various recipients but the spreadsheet will rarely be opened. From what I have read so far, I should create an event in Scheduler that will open the spreadsheet then trigger an email to the required recipients.
The problem is that I have no idea where to start.
Any help will be greatly appreciated.
Serial NumberREFERENCE STANDARDLast Inspection DateDescriptionLocationNext Inspection DateStatus16433-1Visual Inspection19/4/11Wire rope sling 16mm x 3 metre H/S IWRCContainer18/4/12OK

A:Trigger Email based on due date for inspection

I seem to have had a lot of views but no assistance yet so I thought it may be helpful to attach an excerpt from the Workbook I am trying to automate.
Thanks in Advance.

3 more replies
Answer Match 57.12%

Am looking for a way to automatically delete files based on date (files that are 2 days or older) on a NT4.0 server. I'd guess there's some way to do this with a batch file, just not having very good luck figuring it out.

A:Auto Delete Files based on date

Well i think you are posting it in the wrong section it should be under development. I am also curious on this to, So if you find out how send me a PM.

I am also wondering if you could do this with a script? Maybe a VBS script or somthing that will just do what you stated above removed any NT4.0 File thats Older than two days.

2 more replies
Answer Match 57.12%

Alright this one might be a bit of a challenge. I am releasing a complex spreadsheet with my product pricing on it. Is there a way to make the program have a "shelf life" of 6 months or so? I would like to set it up to make it inaccessible to anyone without the password after a certain time period. So basically I would like to create a macro that will disable the entire spreadsheet (without the password) after a specific date so I can release new versions (with the new pricing) without the old versions (old prices) getting thrown into the mix. Anyone have any ideas?? Thanks.

A:Excel Macro - Date Based Protection

Hello CWDENVER, this should be possible to achieve, you can have a date held in a cell in a hidden Protected sheet in the workbook. When the System date = the date in the Cell the Target Worksheet is hidden and can only be made un-hidden by entering a password.
To be effective the Menus would need to Removed from the Workbook as well.
The VBA Editor would need to be password protected as well.

2 more replies
Answer Match 57.12%

I'm building a spreadsheet to compare sports statistics where the match-ups are different every week. I'd like to have a sheet which has each team's schedule including date and "week number". Another sheet will have each team's stats. And the third sheet will show me a breakdown of the match-ups based on the current date. I'm stuck on having Excel recognize which information to pull from based on the date.

A:How do you show data from 1 sheet to another based on date?

Without some idea of how your data is laid out, it's a little tough to give you a specific answer.A common method for pulling data from one range to another "based on" something is VLOOKUP. If you read up on that function, you may find that it will work for you.If not, or if you can't figure it out, you'll need to post some example data so that we know what we are working with. Please click on the following line and read the instructions found via that link before posting your example data.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Answer Match 56.7%

I'm looking for a formula for excel, but can't seem to fine tune one, or find an existing one.

The spreadsheet is for applications. If initial contact happens on say 7/20/2010 and the applicant has 5 days in which to respond, for example. I want a column that posts the due date for follow up automatically, and then also a column that says either NO or YES in regards to Follow Up Needed that also updates automatically based on Date Of Follow Up having passed.

I hope this makes sense. I'm going to attempt to put an example below.

Application Date Applicant Name Date Of Follow Up Follow Up Needed
7/20/2010 John Smith 7/26/2010 YES
7/25/2010 Jane Smith 7/31/2010 NO

EDIT: Well, it won't maintain the spacing, but that should give you an idea of what I mean.

A:Solved: Excel Formula if a date is exceeded

11 more replies
Answer Match 56.7%

Sorry for the newbie question, but here goes:

I am using Excel 2007 and I have a spreadsheet that uses 2 dates -- the buy and sell date. I have a column that calculates how long we held the product. I use an IF statement to put a zero in the column if the product hasn't been sold yet which looks something like this:

IF(ISBLANK(buy_date),0,(sell_date - buy_date))

Now I want to summarize the "time-to-sale". I want to count all "0" sales (unsold), all sales between 1-30 days, 31-60, 61-90, through the whole year. The problem is, I can't use a macro because this spreadsheet needs to be "macro-free".



A:Solved: Excel formula -- by date summary

7 more replies
Answer Match 56.7%

I am trying to find a formula for counting up to a calendar date from a calendar date. That is, column A, row 1 is the umber of days to count up. Column B row 1 is the date I want to count from, Column C row 1 is the result. It would look like this when finished

33 1/3/2008 2/5/2008

Is such a formula possible?


A:Excel 2003 formula for adding to date

Well this does it, but I am not sure if you should use a date diff function

1 more replies
Answer Match 56.7%

I am trying to create job status sheet, and I want the current date to show whenever a cell or row is added.

For example, If I put a job number in B2, I want the date(year, month, day) to show in cell A2 automatically. The IF formula is only formula I can think to use.

A:Solved: Using the IF formula to show current date

Just put the formula =Now() in the target cell. And each time the worksheet will be recalculated the formula will be updated, so no need in and if function.

2 more replies
Answer Match 56.7%

Is it possible to put today's date into the Formula field in the design view of a table?

There is a string that includes that field as its prefix. Right now it's just a static value. The rest of the string includes just an incrementing value.

For example... the field contains A and the count is 000000. Thus the result is A-000000. The result is stored into one of my tables. I would like to be able to use dates so that it would be easier read.

I would like it to say... 103103-000000 ... 103103-000001 (later today)... 110103-000002 (if tomorrow)...


More replies
Answer Match 56.7%


I have some irregular reports that need to run on the weekend following the due date. The due date could fall any where between Monday and Sunday. The formula I am looking for is

Report Due Date Day Report to be Run on
Report # 1 6/27/2011 Monday 7/2 or 7/3
Report # 2 6/29/2011 Wednesday 7/2 or 7/3
Report # 3 7/2/2011 Saturday 7/2/2011

Also each report has different recurrence 40 days, 43 days etc.

Hence the logic will be


Any help will be appreciated.



A:Formula to move the due date to upcoming weekend

7 more replies
Answer Match 56.28%

Hi. I need help creating a .bat file that compares 2 same file on 2 different computers (server and client) on a LAN network..
It compares the same file on a server cpu, and there are 4 possible conditions i think:
- Same date on server > then do no action
- Older date on server> no action
- Newer date on server> action, sync using goodsync command line (i'll work on this)
- Connection with the server down > no action
So it's like this :
abc.txt on client 11/01/12
abc.txt on server 12/01/12

then sync

My question is, how exactly do i compare file on a LAN network?

A:Need help creating a batch file based on last modified date..

Welcome to TSG denywinarto. Hope you find some help here and take the opportunity to help others!

I quit doing Batch files a while back an went to vbs because it seems more robust. That said there is a vbs script in this thread (post #3) that you point at a directory and it makes a text file of file information including modified date and the full path to the file.

If you run this against your two folders (producing the two text file) then write a process that compares the two files and moves data according to your desires. You can do the file move and compare in vbs and not have to worry about the goodysnc piece.

1 more replies
Answer Match 56.28%

Hi all,

I would like to conditionally format the attached spreadsheet so that each alternating week is shaded on whole set of rows.

Also with our roster the week runs Thurs to Wed.

I've looked quite a bit into conditional formatting now. But this one has me stumped.

Thanks for any advice.

A:Excel: Format rows based on date and a few other specifics

7 more replies
Answer Match 56.28%

Hi all,

I am looking for help on VB script to send automated emails based on the date column in excel sheet..

Uploading the excel sheet as well. The email should trigger on dates in column F to corresponding email addressed in column E ..

The email body should say :

Your employee "Column C" is approaching his probation period on "Col D". Kindly confirm if you want to confirm on the date of "Column D" or modify it.

A:Need help to send automated email based on the date field

Have a look at the attached I have written the code necessary however if you have any changes you would like made let me know.

1 more replies
Answer Match 56.28%

I have a shared workbook that I created with 8 different worksheets that all have the same format. I have a "Summary" worksheet with the same format created and a cell (C7) to enter a date. I created a Command Button to click to refresh the data after a date has been entered into C7. I'm having trouble in VBA getting this button to pull ALL entries from the date specified in c7 from all of the worksheets and consolidate it underneath. Everyday my employees enter data into this workbook on different worksheets. At the end of the day, I'd like to enter a specific date into cell "C7," click the command button, and populate all entries made on this date. Can anyone help? I am completely lost in VBA, and have no code attached to the command button at the moment.

A:Consolidating Data from Multiple Worksheets Based on a Date

The code offered below assumes that the Summary sheet is the 9th sheet in the workbook and that you are copying data from Sheets 1-8.BTW...since you said that your are "having trouble in VBA" you might want to review this tutorial. These debugging techniques will help you troubleshoot "broken" code as well as understand code that is found in forums such as this one. Using these debugging techniques can help you write/fix/learn VBA code.
Sub Copy_By_Date()
'Clear Summary Sheet Row 10 through End
Sheets("Summary").Range("A10:A" & Rows.Count).EntireRow.ClearContents
'Set myDate variable to value in C7
myDate = Sheets("Summary").Range("C7")
'Set initial Paste Row
nxtRw = 10
'Loop through Sheets 1 - 8
For shtNum = 1 To 8
'Search Column b for date(s)
With Sheets(shtNum).Columns(2)
Set d = .Find(myDate)
If Not d Is Nothing Then
firstAddress = d.Address
'Copy each Row where date is found to next empty Row on Summary sheet
d.EntireRow.Copy Sheets("Summary").Range("A" & nxtRw)
nxtRw = nxtRw + 1
Set d = .FindNext(d)
Loop While Not d Is Nothing And d.Address <> firstAddress
End If
End With
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

14 more replies
Answer Match 56.28%

I've read the previous post with the same issue, but I'm unable to understand how to use the other codes posted within my product. I would like to send an email based on a date. I will attach my document so it is easier for me to explain the requirement. Columns L37-L45 have due dates - I would like the email to be sent 60 days prior. I have posted some mock emails in R37-R45 and the email message in the EMAIL workbook tab. Any assistance would be greatly appreciated.

Thank you so much!

A:Auto send an email based on date in Excel

Welcome to the board.
I've had to save it as 2003 version but the code works under 2007

See attached my copy of your sheet with the code in ThisWorksheet module.

This just a simple way of doing it and you will have to edit it for your needs but maybe it can put you on the right track.

2 more replies
Answer Match 56.28%

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in
Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!

A:Automatic Email from Excel based on Date in Cell

16 more replies
Answer Match 56.28%

I have a personnel tracker that tracks required documents that need to be renewed every year. There are 4 of these and each has a seperate column for each date. They are J,L,T and U.

I would like excel to scan each of these columns every time the document is opened for any of those dates that are 60-65 days from expiring, I use Now()-300 to check this.

But if the cell is blank I want it ignored, which right now it does not. If it finds 1 or even 80 that are in that range I want 1 email generated to a list of management personnel.

Currently it generates how ever many are blank or expiring. I use outlook.

My data starts on row 3 and goes down to 331, the required and maximum number of people allowed on the contract.

If I can get just one email with a generic message I would be happy. But if I could I would like the info that is actually doing the triggering to be copied and pasted in the email to include the info in A, C and D.

Here is my current code, I would change .display to .send once I have it working. please help.

Private Sub Workbook_Open()
Dim Cell As Range
Dim DateRng As Range
Dim Msg As String
Dim olApp As Object
Dim olEmail As Object
Dim RngEnd As Range
Dim Wks As Worksheet

Set Wks = Worksheets("Work Site Info")

Set DateRng = Wks.Range("J3")
Set RngEnd = Wks.Range("J331")
Set DateRng = IIf(RngEnd.Row < DateRng.Row, DateRng, Wks.Range(DateRng, RngEnd))

'Change this to what you want.
Msg = "This is message is the bo... Read more

A:Solved: Automatic email from excel based on date

16 more replies
Answer Match 55.86%

I use a multi sheet spreadsheet at work to track outstanding tasks.

I would like to write a formula to take today's date and hide the row if it is:
1- less than a week since the last entry
2- hide the row if it from the current month.

I would also like to write a formula that hides the row or column if all cells in use in that row or column have been checked.

Thank you in advance for your assistance.

A:EXCEL - using a date dependant formula to hide rows

Is it necessary to hide the row? The only way you might be able to do that is with a macro, but I think it would be complicated and messy. A more simple way would be to use conditional formatting - for example you could make the text a light grey (or even white) if the conditions are met. It wouldn't hide the row, but it would mean the other rows stand out by comparison.

6 more replies
Answer Match 55.86%

Is there a way to write a formula that adds down the column 1 day to the previous day, but when it hits a Saturday or a Sunday, it goes to Monday?
ie: (a1+1) but by the time it got to April 3 it would know to add 3 to get to Monday
Monday, March 30, 2009
Tuesday, March 31, 2009
Wednesday, April 01, 2009
Thursday, April 02, 2009
Friday, April 03, 2009
Saturday, April 04, 2009
Sunday, April 05, 2009
Monday, April 06, 2009
Tuesday, April 07, 2009

Thanks for any information you can provide.


A:Solved: excel date formula - ignore weekends



If WEEDAY of previous cell = 6 (=Friday) then add 3 else add 1

3 more replies
Answer Match 55.86%

I really need help!!! lol... I'm trying to have the document update from a master sheet which I managed to do via a VLOOKUP, but when the reference date on the document is updated, and I want it only to reflect the past 5-days, rather than the whole amount of information. So at all times it will show the last 5 work days and shift the cells automatically accordingly.

So basically the dates will constantly switch back. So that way tomorrow when I have the results of Sept. 29th, Sept. 22 will no longer be depicted, and the 23rd will take it's place; the 24th will take the 23rd's place, and so on. So it would look something like this...

**5 columns each date with it's own column
****After each day, there will only be 5 columns reflecting the last 5-workdays!

Sept. 22 Sept. 23 Sept. 24 Sept. 25 Sept. 26

More replies
Answer Match 55.86%

I need help with a formula. I have monthly invoicing for contract work. The contracts can be 1 - 5 yr. Each year the contract price changes.

I want to be able to invoice on a certain date and have the contract price automatically put into the invoice based on the contract priced date the invoice falls within.

An example:
If invoice date (E12) falls:
Between date range H20-I20, place J20 (Amount) into D21 (amt) cell.
Between date range H21-I21, place J21 (Amount) into D21 (amt) cell.
Between date range H22-I22, place J22 (Amount) into D21 (amt) cell.

This would go on for a total of five options all based on the invoice date falling within one of the ranges.

A:Excel Formula for multiple date range options

7 more replies
Answer Match 55.86%

Hello experts

I have an excel document with many sheets starting with sheet2

*** column_headers "enquiry#" in B6 and query_expiry_date in E6 (of every sheet)

What I am looking to do is, using sheet1 (which is unused at the moment)
dynamically return 3 pieces of information (in 3 columns, A,B,C for example)
based on expiry dates.

ie: if the expiry_date<today() then return sheet#, enquiry# and the date that expired.
this is for each enquiry and each sheet and if I can sort them in order of expiry date, that will be owsome.
Can that be done using lookup? or maybe better in VBA? a push in right direction or example
of code snippet is well appreciated.
Thank you

A:Solved: excel(VBA) returns data based on expiry date

12 more replies
Answer Match 55.86%

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in
Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!

A:Automatic Email Reminder from Excel based on Date in Cell

Please do not post duplicate threads.
One thread per issue.
Continue replies for this issue in this thread:
Thank you.

Closing thread.

1 more replies
Answer Match 55.86%


I am in dire need of a solution wherein I want outlook to shoot an automatic email based on date from calendar or excel. Basically I have some users assigned some privileges which are assigned for a specific period. I need the mail to go out to them may be a day or two prior to the target date.

I came across this thread:

I copied the code the in outlook VBA editor (i already have one script there and since being new to this i do not know if two scripts would be ok to have in there.... ...ScreenShot attached). However just to try when i ran the rule it gave me an error (screenshot attached).

I can't reply to the same thread since that is closed.

can you please help in this regards...


A:Solved: automatic mail delivery based on date/time

9 more replies
Answer Match 55.86%

Dear All,

I am creating a tracker file for Purchase Orders in which end date in column 'J' is very important.
Column 'J' has following conditional formatting
1. If cell date is in between todays date + 14 days --- YELLOW
2. cell date <= today's date ------------------------- RED
3. cell valus > today + 14 days ---------------------- GREEN

So now when the cell turn Yellow I want the excel should send the mail to the concerned person whose mail id will be mentioned in same row of column 'G'

One more requirement there should be two mails. Reminder 1 & Reminder 2.

I use officer 2010 & mail is outlook 2010 & OS is windows 7.
This file will be on share point. This file may not be opened everyday.

Please reply with the procedure as I am not a programming/ IT person... I may not understand all terms.. please try to simplify the response.
Thanks in advance for all the help.

A:Auto mail from Excel to Outlook based on date in cell

Hi there, welcome to the board!

You'd want a location to mark when an email was sent. I'm assuming you want a worksheet change event for this, which will basically always run when a cell on this specific worksheet is changed. There are other events you could use to fire it off, like the calculate event. You could, if you wanted to, assign this functionality to a button, but then it wouldn't be automatic.

The below code does what you ask. It goes in the worksheet module of the worksheet your data is on. To get to it, right click the sheet tab name and select 'View Code', then paste this there.

EDIT: The locations to mark when an email was sent (btw) were columns K and L, as you'll see them set as constants at the top of the code. You can change those letters to any column you want to house it in. It just puts the current system date in those cells, and that will be checked when the values in column J are checked. If nothing is there it assumes an email hasn't been generated yet and will do so. But if it has a value - any value, it will ignore that row.

Also, I assumed a 'yellow' highlighted value was your first reminder, and a 'red' highlighted value was your second reminder. It uses this as text in the subject and body of the email.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Const ReminderOne As String = "K" 'column letter
Const ReminderTwo As S... Read more

1 more replies
Answer Match 55.44%

I haven't been able to quite get my functions right on this one and am hoping for some guru help.

I have a spreadsheet with dates in Column A (Contract Received) and Column C (Contract Filed). Not every cell has a date in one or both fields, as it is a tracking tool for work in progress. However, a contract must be Received before it can be Filed, so C will (er,should) never be blank if A is populated. I want to know the number of days lapsed between date Received and date Filed, and if date Filed is null, then number of days from Received to today's date. If the contract has not been Received, then no calculation should be performed. In spite of requests not to, users sometimes put text notes in these fields, so would be nice to ignore text. At bottom of column, I need to get an average of the time lapse on all filings to track productivity. This doesn't sound so difficult to me, but somehow I haven't gotten it quite right in the last two hours and it's time to ask an expert. Thanks in advance for your help!

- k

A:Excel 2010 Date Calc Formula, Ignore Blanks

Hi k, welcome to TSG.

Try this and let us know if it gets you close to what you need:


It basically starts with column A and determines whether there's a number there (a date, as opposed to text or blank). If not, it doesn't do anything, but if so, then it does the same thing with column C. If C has a number (date) then it subtracts A from C. If not, it subtracts today's date from A.

Let me know if I've misunderstood the question.

1 more replies
Answer Match 55.44%

I need a formula....I have 3 ranges of cells (365, one for everyday of the year).In column A I want the cell to say either "CURRENT" or "LEAVER". I want this to say current unless told otherwise from a cell on another page specifying the particular leaving date, which will obviously vary person to person, without changing the status in the cells where the employee was active.Column B is the datesColumn C is Information I need calculating, but only if the employee status read "CURRENT", which is an easy SUMIF formula.But column A I am struggling withSo far I have an IF function we that I can gives me both "CURRENT" and "LEAVER" :=IF('New Starter Info'!$H$2<>"","LEAVER","CURRENT")But the date changes every cell rather than just the ones after the leaving date.Any help will be great

More replies
Answer Match 55.02%

Hi Everyone!

This is my first post here. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a project is 7 days away from the current date. The script should preferably run automatically every time the PC is running. without the excel file necessarily open.

In the attached excel file, An email should go of to -email address (Col. D), with subject "Project (Col. B) is due on Due date(Col. C)", and body "Dear Name(Col. A), please update your project status".

Also, the script should put a check mark on Reminder sent column (Col. E) after the mail is sent, the script should also check if the value of the cell is blank before sending email.

I have scoured the forum for similar problems, and although I found a number of of threads,I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,


A:Automatic Outlook mail due date reminder based on Excel file

8 more replies
Answer Match 55.02%

I need help trying to come up with a formula to calculate the sum of values (row 3), for the previous Friday to Yesterday's date i.e. Wed would sum Friday to Tuesday; Thursday would sum Friday to Wed...and Monday would sum Friday to Sunday.

My example is not posting correctly...but essentially Row 1 has Day headers (Monday, Tuesday, etc.); Row 2 has the actual date that corresponds with day header (3/7/2011, 3/8/2011, etc.); Row 3 has the values that I am wanting to sum according to criteria mentioned above. So for TODAY, 3/9/2011, I would like to sum the values of Friday, 3/4/2011, through Yesterday, Tuesday, 3/8/2011...and so on...
Greatly appreciated!

A:MS Excel 2007: Formula needed for sum of values from Friday to current date

9 more replies
Answer Match 54.6%

Firstly, let me apologise, I'm a complete Access noob & am trying to teach myself as I've got no one to help me in person.

I'm trying to build a Time Sheet database that should record daily duties via an entry form:

This form updates an underlying table:

The date field of the "Work_Hours" table is:

However, workers get paid fortnightly and all their entitlements, allowances & overtime are calculated on these fortnightly periods. To be more specific, the workers are paid for a certain amount of overtime for each fortnight upfront & when they exceed that, then they are entitled to all excess time back in TIME OFF. So it's pretty important that each day's duties are assigned to the correct fortnight period so that their overtime & so-forth are calculated correctly.

Each fortnightly period is numbered according to year. For example, each year there are 26 fortnightly periods, so as an example today (3rd October 2013) falls in period 2013-19 (23rd September 2013 to 6th October 2013).

I've created a table called:

Which has the following Fields:
"ID" (Auto-number)
"Period_Start" (date - the first day of each new fortnight period)
"Period_Number" (number - eg: 2013-19)

Back in the "Work_Hours" table, I inserted a new (Number) FIELD called:

and set ... Read more

A:Solved: MS Access - How to get a field auto-update based on date entered in a form

9 more replies
Answer Match 54.18%

HI guys,

I am using Microsoft Excel 2003 and am trying to create a scheduler for my work place.
What I have to do is allow the user to enter data in the input sheet (by input meaning Dates) and then accordingly scan the schedule sheet and find the date entered on the input sheet, following which print the appropriate schedule opposite that date for that particular project.

For Example .....
If a person is working on a project called apple in cell D (effectively making it the 3rd project in the list) and the user enters the project start and end dates as 5th June and 13th June. So what I have to do is scan the Schedule sheet for these dates and enter the appropriate text of Start and End before these dates in the D column (i.e under the respective project).

How to do this I am clueless as I am a complete idiot as far as excel is concerned. SO any idea or any sort of help as to how this can be achieved will help.

The sheet must be automated as far as possible and there are about 25 to 30 projects which need to be updated into the sheet before the final output.

Thanks in advance.

A:Solved: Formula to find a date and enter schedule in a specific cell in Excel

13 more replies
Answer Match 54.18%

I have a spreadsheet that shows all of the upcoming events at my work (I've attached a very basic example of the way the spreadsheet is set out).

When an event passes, I'd like the spreadsheet to automatically change the formatting of the rows that contain events that have already passed.

For example, today being 14.12.09, an event for yesterday (13.12.09) would be highlighted in red to show that the day has passed.

If anyone can provide any detail on how to achieve this, I would greatly appreciate it.

A:Solved: Conditional Formatting Based On Date Range In Microsoft Office Excel 2007

Select your data, then Format > Conditional Formatting.

Condition 1; Formula is:


Click Format > Patterns > red colour > OK.

Click OK.

2 more replies
Answer Match 50.82%

...hope I named the thread right.

Good day everyone. This one thing in Excel has been driving me crazy for a couple of hours now. I've looked all over the internet trying to find the solution and still couldn't. Hoping to find the solution here. otherwise I might stay up all night tonight, trying to make this work

so I'm putting together this custom spreadsheet for my own little needs. let's say I have A1, B1 and C1 cells. in the C1 cell I want to display "1" if the numbers in A1 and B1 are equal and they are not equal then display nothing. so the formula I'm using is


and is there are any numbers in A1 and B1 excel interprets everything just fine. but if if there are no numbers yet, it takes two empty cells as having the similar data and still gives me "1" in C1 cell. so how do I make so that Excel does not execute the IF formula before I enter the numbers in A1 and B1. or is there another of of not getting the "result" before actually putting in the numbers?

hope that someone can help me with this thing.

A:Solved: Excel 2007, stop IF formula if no data entered.

12 more replies
Answer Match 50.4%

Hi Friends,

I have a Zip folder as Y:\, its updating/modifying daily in the night.
I want a script to extract the Zip folder if and only if the zip file is modified yesterday.

I have a script for unzip a folder from source directory to destination.

"D:\MainScripts\7Zip\7-Zip\7z.exe" x -o"D:\" "Y:\"

Please Help me!!

A:[SOLVED] Unzip a folder based on the date modified of the folder

cant you use robocopy to make a copy/move the zip to another folder based on date and then unzip?

2 more replies
Answer Match 50.4%

Good day,

I was looking for someone to help me in this:

Please see the attached excel file, which automates Groupwise mails for due date reminder. However, it generates individual mails for each due items, whereas, I want one single mail with all due dates. How do I achieve it?

More replies
Answer Match 50.4%

Hi ! This refers to the subject of "Automatic email from excel based on date", and relevant thread

This was fantastic. Only one issue remaining.
This is generating multiple emails and even the items not due are generating blank mails.
I would like to generate only one mail with different items different expiration dates.
After that group the identical addressees and compose one message with all the relevant data.
Awaiting kind help.

A:Need Help reg "Automatic email from excel based on date "

7 more replies
Answer Match 50.4%

While using XP, I used Pegasus mail as my e-mail client. Pegasus's mail filtering options included the ability to set a duration on a mail folder such that any message in that folder, older than the specified duration, was automatically moved to the deleted mail folder.

Is there any way to achieve the same thing using Windows Mail under Vista?



More replies
Answer Match 49.14%


I use the Word short-cut icon 'Insert Date' when I create a new letter. I DO NOT TICK the update automatically box within the shortcut.

The problem is ...if I want to pull up this letter in say 6 months time and re-print it - the letter opens with the current date rather than the original date of writing. Therefore - I have no real proof that this letter was written 6 months ago unless I kept a hard-copy!

I particularly find this annoying when dealing with contract letters. So lately I have been using the basic version of a date by manually typing in 23/11/2008 rather than the fancy Sunday, 23rd November 2008 as the former stays constant.

Any cures for me?????


A:Stop Date updating itself in old Word Docs.

8 more replies
Answer Match 49.14%

Windows 7, Windows Live MailWhen I print out an email there is a date stamp at the bottom of every page. How do I stop the date being added to the email?

More replies
Answer Match 49.14%

Hello all,

I'm running the following config:
Windows 2000 SP4
IE 6.0.2800.1106

I'm experiencing the following problems:
1. My firewall was prompting me to Permit or Deny access several days ago. Unfortunately, I wasn't there when this happened, so I don't know who wanted access.
2. Whenever I try to access GMail in IE (set to log me in automatically), IE automatically refreshes the page indefinitely (I can see the long cryptic string in the address bar changing). In Firefox, this problem does not occur.
3. The date of my calendar has changed from July 13, 2006 to December 11, 2006.
4. HijackThis mentioned a login.scr in my RunServices (which I removed) and my screensaver was set to a screensaver called MooDays. I found a moodays.exe and moodays.scr in my systemdir and threw them away. None of this fixed the problem.

I ran a full McAfee virusscan, which turned up zilch.

HijackThis gave me the list below. I checked it myself on and found nothing strange (except for the login.scr, which as you can see is off the list).

Can anyone recommend what I should do?

Thanks a bunch,

cronopio, downloading the latest Windows security updates.

Logfile of HijackThis v1.99.1
Scan saved at 7:44:44 PM, on 7/13/2006
Platform: Windows 2000 SP4 (WinNT 5.00.2195)
MSIE: Internet Explorer v6.00 SP1 (6.00.2800.1106)

Running processes:
C:\WINNT\syst... Read more

More replies
Answer Match 49.14%

I receive periodic Word (.rtf) documents from a property rental agency whenever my property is rented. The document contains the dates rented, the renter's name, etc. There is a "date field" in each document which automatically shows today's date, rather than the date the doc was created and sent to me. If I want to forward the doc to the renter a week later how do I stop the date from updating when I send it and stop it from updating when the renter opens the file?

A:Solved: Stop Updating the Date Field

6 more replies
Answer Match 49.14%

I have a tab delimited .txt file. One of the fields has 5-2056 in it. When I open the file in Excel, it gets changed to May-56. How can I stop this? I can't edit the data, and I can't format the cell (it's not an actual cell/spreadsheet).

A:Solved: Excel, stop changing to date

If you want the 5-2056 kept as is, then do the following.
Select all the cells with data in
Click on Data, Text to Columns
Delimited, NEXT
Select the delimiters you want to apply e.g.TABS, NEXT
The 5-2056 should now be shown in its own columnand the heading should say general. If so just click on Finish
It should now be in its own columns and the 5-2056 will appear just as that.

hope that works foryou.

Come back if you need more clarity.

3 more replies
Answer Match 48.72%

Firefox changes the date on Bookmarks.html every time it is run, even when changes aren't made to the bookmarks file. I can simply open Firefox and then immediately close it without using it at all and the date on the Bookmarks file is changed.

Is there a way to stop this and have the time stamp changed only when the file is modfified?

A:How to stop Firefox changing date on Bookmarks.html

6 more replies