Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

excel today countif over expired date

Hi

I use a large SS that has expiry dates in column H for "live" jobs. I have a
stats screen that counts the amount of entries that are beyond this date
using a countif statement. For example: =countif('Sheetname'!H:H,today())-1)

I repeat this upto -7 and that is excels limit on the amount of statements I
can perform. So, I can only see the amount of jobs that are a max of 7 days
over the expiry. Im sure there is an easier calculation for this that will
count more than 7 days past today?

Please help!!!

John


Post your answer or comment

comments powered by Disqus
First off, I just want to say that I've searched the board for about an hour before posting this, and while I've found a lot of very similar things, I still can't get my spreadsheet to do what I want. I'm pretty new to the advanced features of Excel so any help would be greatly appreciated.

I have a spreadsheet which tracks the dates that someone completed training and I need to be able to easily see people who expire in 60 days or less. This training has a currency period of 365 days. In my spreadsheet I have over 100 dates in the F column starting at 2 and going to 121. What formula can I use in conditional formatting to accomplish this?

I am tracking expiration dates for training. With numerous people working
for me, tracking their training is vital, and the easiest view for all
training classes required is on Excel. I am wondering if Excel can
automatically change the color of a date that I input as the expiration date
approaches and as it passes.

I appreciate any help. If it is capable of doing this task, I cannot find
how to do it.

Hello,

I have five columns shown below, with sample values after the colon

(Col F) Today's Date: 04-28-06 {this will be using the =TODAY() function}
(Col G) Contract Date Signed: 03-28-06
(Col H) Duration (Months): 12
(Col I) Expiration Date: 03-28-07*
(Col J) Days Remaining in Contract: 334*

* these are hypothetical, they are not correct

If the user puts 12 in column H, for the duration, the result will be
03-28-07.

But what if someone puts something other than 12, like 8, 6, 7, or 13? I
need someway to be able to get Excel to tell me the EXACT day that the
contract will expire, AND how many days are left in the contract. BTW, the
user will ALWAYS be entering whole numbers, not decimals, eg. 6.5 months.

Thanks in advance for your responses.

Phil.

Hi there

I am attempting to create, what might be a very simple macro in VB for Excel 2007, but I am having a lot of trouble.

I have two columns that need to be referenced and conditional formatting applied to one of them (column K).

The first column (H) shows the stage. I am only concerned with highlighting items in column K that are not listed as Engaged or Lost & Leaked in column H. The ulimate aim is to highlight expired dates (dates less than today) in column K for possible work that has not been completed.

I have tried to put it as simply as I can below:

'If cell in column H is not equal to engaged or lost and leaked then look at column K

'If cell in column K is blank then do nothing

'If cell is less than NOW()

This needs to loop until there are no values in Column H.

Any direction or help with this would be much appreciated!

Anna

I am writing a really neat excel spreadsheet and I'll be sharing it with everyone in my company. However, this is my spreadsheet, worked on for better than 40 hours on my own time. I'd like to maintain SOME sort of control over it.

What I was hoping was available is some sort of an expiration date on the spreadsheet. Let's say in 6 months from now when a certain day is hit, the sreadsheet will cease to work. As long as I'm working there, I will continue to update it. I would normally never be this way, but the company has been up to no good lately. If I were to leave the company, I would like this worksheet to stop working in some amount of time. Because this spreadsheet will be on many computers and the server at work, there is no way I can take back my spreadsheet once I put it out there.

This is my first post, so thank you in advance to anyone who shares with me.

Excel Kurt

Is there a formula for COUNTIF where I can count the number of entries BETWEEN two dates but also BEFORE today's date?

For example, I have this table:

Training Schedule
Training Location Training Date
A 2012/01/25
B 2012/01/26
C 2012/02/28
D 2012/02/29
E 2012/03/01
F 2012/03/27
G 2012/03/28
I 2012/03/29
J 2012/04/24
K 2012/04/25
L 2012/05/02
M 2012/05/03
N 2012/05/24
O 2012/06/20
P 2012/08/29
Q 2012/08/30
R 2012/10/23

And I have this quarter guide:

Quarter Guide 2011-2012
Quarter Start Date Quarter
1/1/2011 2011 - Q1
4/1/2011 2011 - Q2
7/1/2011 2011 - Q3
10/1/2011 2011 - Q4
1/1/2012 2012 - Q1
4/1/2012 2012 - Q2
7/1/2012 2012 - Q3
10/1/2012 2012 - Q4

So basically I need to calculate the # of workshops in each quarter that have been delivered before today's date (and are not just scheduled). Determining the number of trainings scheduled is fine but I can't figure out a countif or sumproduct (or whatever) where I can count the number of trainings in each quarter that have occurred before today's date. Right now I have manually entered 8 but I want it to be automated. So like on May 1, Quarter 2 should read "2"... then on May 4, Quarter 2 should read "4" for delivered based on the training schedule:

Q1 Q2 Q3 Q4 Total
Total Number of Trainings Scheduled By Quarter 8 6 2 1 17
Total Number of Trainings Delivered by Quarter 8 0 0 0 8

I tried a formula like this COUNTIFS(Table1[Training Date],">="&F7, Table1[Training Date],"<="&F8) where F7 and F8 are the start and end dates of the quarter but of course this just gives me all the trainings in the quarter, irregardless of if they've occurred before today's date. How can I add that criteria in?

Thank you very much for your help! It's very much appreciated.

Does anyone know if Excel has a formula that will highlight an expired date
in red based on the current date? Example. Start Date = 1/4/04 and End Date
= 1/3/05. Today = 1/4/05 therefore the End Date has now expired and I would
like that to show up in red text in order to flag expired dates. Any
suggestions? Thanks Donna

I am trying to create a database with due dates for work. For Example.

Due in 7 Days =COUNTIF(A7:A40,"<="&TODAY()+7)-COUNTIF(A7:A40,"<"&TODAY())
Due in 30 Days =COUNTIF(A7:A40,"<="&TODAY()+30)-COUNTIF(A7:A40,"<"&TODAY())
Due in 60 Days =COUNTIF(A7:A40,"<="&TODAY()+60)-COUNTIF(A7:A40,"<"&TODAY())
Due in 90 Days =COUNTIF(A7:A40,"<="&TODAY()+90)-COUNTIF(A7:A40,"<"&TODAY())
Lapsed<Today =COUNTIF(A7:A40,"<"&TODAY())

I think these work for me in excel, but I'm not a master at this and for some reason when I read my functions I swear they are contradicting themselves. The reason I feel the functions contradict themselves is because I can remove the -countif and it still gets the same answer.

For the date ranges 90, 60, 30 & 7 I do not want dates counted that are less than "today" or ex. = a negative number. They would be considered "Lapsed"

Please see the attached file. Any help would be greatly appreciated!!!!!

Kevin

My wife needs a formula and I hope that I can explain the situation properly. She is building a contractor’s list of employees. She wants to be able to input the date of issue (7/2/2011) in the first column and have excel calculate the expiration date of one year in the next adjacent column. The kicker is that MSHA gives each vendor a grace period until the end of the month. One would think the expiration would be (7/2/2012) but they would like to see a date of the end of the month (7/31/2012)

Dates are tricky things and I am not sure how to build this formula. I don’t know if the DATEDIF argument would work or not. Any help would be greatly appreciated.

Excel 2010 question: I have a table that I'm trying to write a validation formula (using Excel formula and avoid VBA if possible)for. In the example table below - the "Assignment" given to the unique combination of "Account" & "Sub-account" for an over-lapping Start and End Date must be the same. A conflict error is thrown if that rule is violated. So, "2" is not a conflict because the date range is within "1". "3" is not a conflict because combination of "Account" AND "Sub-Account" is different. "4" and "5" are conflicts because a different "Assignment" was given for "Account" and "Sub-Account" for a over-lapping date range. "7" is a conflict because a different "Assignment" is given for the same "Account" & "Sub-Account" and over-lapping Date range.

NO. Account Sub-account Start Date End Date Assignment CONFLICT?
1 PET DOG 1/1/2012 1/31/2012 HOUSE1
2 PET DOG 1/15/2012 1/17/2012 HOUSE1 No
3 PET BIRD 1/1/2012 1/31/2012 HOUSE1 No
4 PET DOG 1/15/2012 2/15/2012 HOUSE2 YES
5 PET DOG 1/1/2012 1/31/2012 HOUSE2 YES
6 PET CAT 2/15/2012 2/29/2012 HOUSE1 No
7 PET CAT 2/15/2012 3/15/2012 HOUSE3 YES

Hey all,

I've been looking through the form but I can't seem to find what I'm looking for. I want to set an expiration date and then every time my addin is ran check the date. If the current date equals or is greater than the expiration date lock the program.

I was thinking like setting the end date in the windows registry at some point and then checking the dates to validate.

Also does anyone have any ideas how I can only set the date once (like at first run of the addin) and every other time only check the date instead of setting the end date all over again?

How can I do something like this?

Thank you

Is it possible to render a spreadsheet un-openable or locked out totaly for editing by use of an expiration date? Example: I have a pricing sheet to give a customer. I want it to expire 60 days from today and become un-openable untill a new password is given.

Thanks for your help.

s

Please direct me on how to link "today's" date to the expiration date within the cell.
I would like to have the ability to automatically highlight the information in the entire row 14 days prior to the "expiration date" label shown in the cell. Any assistance would be greatly appreciated.

Thanks,
Saudi

Hello! I hope I can make this a clear question! Thank you for your help!

I have a cell that has a specific date, March 10th, for example...I would like another cell to have an X in it if todays date is after march 10th. This has to do with expiration dates of customer records. Thanks!

I have data in a cell that I would like to code with a color, to corresponds with a date. If the cell is colored pink, it means the percentage in that cell expires on a specific date (ie Sept. 1, 2009). I would like all pink cells to have a comment i can place my mouse over, showing this expiration date. .... that or I would like to be able to set an expiration date for the cell at the time I enter the data. I don't want to have to put a date in a seperate row... i just want to be able to mouse over and see the date.
Does this make any since? If not, I'll try to rephrase.

I have looked through your forums the last few hours. I have found similar code to what I'm looking for... but not quite.

I have a date in B2 (10-May-07)
I have an expiration date in C2 (10-May-08)
I want D3 to show months to expiration first (in this case 7)
Then I want it to highlight in yellow 90 days out
Then I want it to highlight in orange 60 days out
Then I want it to highlight in red 30 days out

I use Excel 2007, however I want the code to work with 2003.
Thanks for your help.

For my work Im keeping an excel sheet which lists all lists all action points from our meetings. What Im wanting to do is highlight a field in which an expiration date is set too. Also each action point can have a severity classification to it which would effect the expiration date also. For example I have one action point in which is classified as a minor one and should be resolved in 10 days, and another action point with a major classification and should be resolved in 20 days. Once the expiration dates passes I would like the field to be highlighted red. Any ideas on how to make this work?

Aside from using the date functions in either excel or VB, is there a way to set an expiration date for a file so that after a certain date the file will become inactive, unavailable, etc? Or do you have have VB wipe out data if the date meets this criteria. haven't tried this before, looking for the quickest way to accomplish this.

thanks

Hello...

I have a spreadsheet where we track our contractor's Worker's Comp and General Liability insurance certificate expiration dates. I want the expiration date to highlight in red if it is expired and to highlight in orange if it will expire within 30 days or less.

I have attached a testing sheet similar to what I'm working on. The F column has the dates that need to highlight. Please help! Oh, and I have MS Excel 2010. Thanks for any help you can offer!

Having a problem building a formula to calculate the expiration date of a medical certificate for a pilots in a club I belong to.

Pilot medical certificates have a duration based on age of individual and class of medical certificate and class of pilot certificates.

A third class medical certificate is good for 24 month if the individual is 40 or over. On July 24, 2008 they changed the duration for under 40 to 60 months, and anyone that had a exam done that was younger than 40 prior to 24 July, 2008 will have there certificate duration automaticly extended to 60 months. Pilot certificate does not matter on a third class medical certificate.

I have a couple of pilots that will be close to 44 before there extended certificate will expire, so I need to calculate off there birthdate to see if they were younger than 40 on 24 July, 2008 so there duration dose not go to 24 months once they turn 40.

If the medical certificate is a second class and the pilot holds a commercial pilot certificate the duration for any age is 12 months, if the pilot holds a private certificate then the duration is 60 month under 40, 40 and over, 24 months.

We currently do not have anyone with a first class medical certificate, but theres is simular to second class medical certificates, except that if they have a airline transport certificate under 40 is 12 months, 40 or older 6 months. For a commercial pilot certificate with a first class medical any age 12 months, under 40 for private 60 months and 40 and over 24 months. First class is also affected by the new rule that will extend the duration to 60 months for those that were younger than 40 on 24 July, 2008. I would like to have this in the formula just incase at some point in time a person should join that has a first class medical.

As you can see this is getting very complicated, at least for me.

Thanks for any help.

Hi everyone...

Thanks to anyone in advance who can help me here.

Im designed a form in excel, which has a calendar date picker to select a desired delivery date for the user, which makes it easier to use for the user, than using a text box.

Ive searched a number of websites and so far havent seen what I need.

I want the calendar, once my form has been opened, to automatically change to the current date.

So in summary, the code, I believe would look something like this:

Sub Submit_Click()

Calandar2.value = date

//more code etc..

End sub
I seem to error with this...

Is there an alternative method? For some reason it always seems to only remember the previously used date.

Any help would be great.

How can I set up a program in Excel to help me track Contract Expiration dates?

Hello I am having a problem that just started in excel today. I entered the date 030909 and then format the cell to date & it changes my date to 8/15/84
I have tried converting the cells first but same problem. Since then I have went to 3 other computers with Excell 97 & 2003 and all of them are doing exactly the same thing. This problem just showed up today!! we have been enter data like this for years & never had a problem like this. Did something change?? thanks in advance for any help

I have an excel program that I want to set an expiration date on it, such as 30 days from the first time it's opened. After 30 days, activeworkbook.close

any help at all would be appreciated


No luck finding an answer? You could always try Google.