# Conditional Format - Dates Greater Than 18 Months And 24 Months?

Dec 6, 2013

I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:

1) 18 months from the date in the cell needs to be highlighted yellow

2) 24 months from the date in the cell needs to be highlighted red

ADVERTISEMENT

## Located Set-up Dates That Are Greater Than 6 Months

Aug 10, 2009

I have a sheet where I need to located set-up dates that are greater than 6 months. I dont know if this matters but the cells are not in consecutive rows but are all in column F. I have tried the serveral ways and nothing is working.

## Excel 2007 :: Conditional Format Based On Months?

Feb 20, 2014

In Column D of my work book are a list of months, I'm trying to use conditional formatting to do the following:

Highlight green the cells in Column D where the month is due (i.e the month is now).
Highlight orange the cells that are one month passed the due month.
Highlight red the cells that are two or more months passed the due month.

I'm using Excel 2007.

## Elapsed Months (many Months Have Gone By Since Todays Date)

Jul 19, 2009

Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.

## Add Months & Return Decimal As Years & Months

Jul 31, 2007

I am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.

## Finding Months Between Two Dates?

Dec 23, 2013

finding a formula to calculate the number of months between two dates. I've attached an Excel spreadsheet with sample data. Some are in the future, and some are in the past.

Excel Help.xlsx

## Using Dates And Months In IF Statement?

Aug 10, 2014

I was wondering if this can be done we have a monthly award program so if a date was entered for that drive on this month it would = no if blank =yes but needs to reset each month?

=IF(AND('Driver'!A51:A1000)="",IF('Driver'!A51:A1000>='Driver Bonus'!A3,"YES","NO")) This brings back a #value! error

=IF(AND('Driver'!A51<=A3,'Driver'!A51>=A3),"NO","YES") this works but only for that cell i need to also recognize cells A51:A1000

=IF(MAX(INDIRECT("'"&A8&"'!A51:A1000"))<A3+1,IF(MAX(INDIRECT("'"&A8&"'!A51:A1000"))<>A3,"YES","NO")) this also works but only for 1 day

## Years And Months Between Two Dates

Aug 7, 2009

The issue is i want years and months between two dates which are not in computer language. Date like 2008/12 and 2010/01. File is attached for you reference

## Grouping Dates Into Months?

Feb 27, 2013

I have a table with model numbers as column A, and Row 1 contains dates, each columns contains net sales, my question is there any simple way to group all dates into months and years, i have tried using a pivot and it doesnt work correctly Screenshot (1).png

## Months That Occur Between Two Dates

Jan 13, 2010

I would like a formula (if it is possible) that will list which months occur between two dates;

i.e
Start Date (Cell ref A2) = 01/01/2010 (in the dd/mm/yyy format)
End Date (A3) = 02/05/2010

In cells D2:O2 I have the months Jan-Dec. In cells D3:O3 I would like a "Yes" to appear if the above month occurs between the dates in A2 & A3. In this example would like a "Yes" to appear in cells D3, E3, F3, G3 & H3 but not in the other 'Months' appropriate cells.

## Counting Months Between 2 Dates

Oct 7, 2011

I need a formula to count the months (including part months) between 2 dates.

So i want 01/04/11 to 5/5/11 to equal 2

I have tried a few formulas that get the answer 1 but i need it to count the part month too.

## Count Dates Less Than 3 Months Old

Nov 28, 2013

I have a column of dates, and wanted to count how many of them are within 3 months of todays date.

I wrote this obvious formula, or so I thought, as it displays zero results, when here should be loads.

=COUNTIF(A:A,>(TODAY()-90)")

## Years & Months Between Two Dates

Apr 12, 2007

Which formula should I use to return years and months between two dates.

4/1/05 7/30/25

## Calculate Months Between Dates

Aug 21, 2007

I am building a template to auto populate budget value by straight line based on task start/end date criteria. some tasks could give me correct duration distribution, but some don't. I am looking for If function, which could give me correct monthly allocation budget. Here is an example of what I am trying to achieve. task 0110 duration is from 09/15/07 to 05/24/08, duration month is 8. budget \$34,465supposed to be allocated to 8 month starting from Sept, 07. but my if logic return 9 month instead.

## Difference In Months Between 2 Dates

Sep 27, 2007

say C2= 22/02/2007. in cell C4 When I use this formula =TODAY()-C2 to get the difference between today and a past date the result the formula give is 07/05/1900
i would like to receive the difference in Months between the past date and today? I.E. HOW MANY MONTHS IN BETWEEN

## Find Number Of Months Between Two Dates

Jun 17, 2014

i have column A and B with list of dates. Column A has earlier dates when compared to column B.

i am trying to find a formula with which i can know the difference between the dates in "number of months".

Ex: Column A has 06/01/2014 and Column B has 08/30/2014. The difference between the dates in terms of month is 3 which i need in a formula.

## Organize List Of Various Months And Dates?

Jun 18, 2014

Is there any way to organize a list of various months and dates

First by creating a column of just the months, then by all of the dates, serperated by commas?

## Count Number Of Months Between Two Dates

Mar 20, 2014

I have a problem again with one of Date Dif function " =DATEDIF(I5|J5|"m") " in attached file i have this function applied to calculate number of months in between two given dates but it seems this function is not working properly or i am missing some info in this to make it run properly. How to fix this function or give me a proper one which can solve my problem to calculate accurate months between these dates in attached file...

Excel Date problem.xlsx‎

## Count Number Of Months Between Two Dates

Oct 31, 2012

i have first date in cell a1 and second date in cell b1. I want formula to to how many months are in between these two dates,, for example

first date: 1 jan 12
swcond date: 5 apr 12
result: 4

## Adding Weeks Or Months To Dates

Dec 17, 2006

a sample of my sheet first....

## Calculate Number Of Months Between Two Dates

Oct 5, 2007

Is there a formula which would show the number of months between two dates eg.

15-Jan-06 to 30-Nov-07 = 23 months

## Find The No. Of Months Between The Difference Of 2 Dates?

Feb 21, 2009

what is the formula to convert the date in MMYYYY into some numbers so that I can derive the difference between 2 dates?

For example:
I have 2 dates 31-Jan-09 and 28-Nov-05 in Column A & Column B respectively. I would like to derive no. of months between the difference of the 2 dates which in this case is 38 months.

## Number Of Decimal Months Between 2 Dates

Aug 19, 2009

I'm using the Calendar Control to determine 2 dates. 1 Starting Date and 1 Ending Date. I'm trying to determine the total number of months between those two dates down to 2 Decimal Places. I also need to get the total number of weeks and days but those are much easier and I've been able to work those out by the following:

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ........

## Adjust Dates - Previous Months

Jul 25, 2006

I have a worksheet that displays totals from a master worksheet. At the bottom of the summary I would like to have the three previous months totals displayed from one column. I know that I can achieve this "quick and dirty" by just using VBA to display what I want, but I wanted to see if there was a better way of achieving this.

I searched and came up with a formula like[code = TEXT(H4,"MMMM"[/code] This will diplay the month name like I want, but there are also three other cells that I need the previous months in. Is there some type of formula that will autmatically change these values once the date in cell H4 changes?

## Calculate Day, Months & Years Between Dates

Dec 20, 2006

I have two columns with dates (and times) in that I am trying to define how many days, hrs and mins have elapsed i.e. A1 has 12/12/06 21:00, B1 has 17/12/06 21:00. C1 has B1-A1 and is custom formatted to show as dd"days" hh"hrs" mm"mins". In this case it will therefore show as 5days 0hrs 0mins. Which is correct.

However, if more than 1month has elapsed then the format m"m" d"days" h"hrs" m"mins" does not work. For example 17/03/06 03:00 to 20/12/06 07:00 shows as 10m 4days 4hrs 00min, which it clearly isn't.

I know the reason it does this is because it calculates the difference between the two times and adds that to it's 0 value, which in my format is 01/01/1900 00:00. therefore when it adds 277days (the answer) it becomes 04/10/1900 04:00, so my formatting is just calling the month value ('10') and the day value ('4').

I understand the reason it does this, 277 days on from 01/01/1900 is indeed Oct 4th, but 277 days on from 17/03/06 is not 10months and 4 days as there are different length months in between. It also seems to add a month on, possibly because the format for 'months' is between 1 & 12 and therefore cannot begin at 0?

Does anyone know if it's possible to force excel to work out the correct number of months and days have elapsed between two dates and not apply it to 01/01/1900? Or any other possible solution, maybe with a different custom format?

## Count Of Valid Months Between Two Dates

Jan 22, 2007

Attached is the spreadsheet where I was trying in to accomplish the following.

1. I have two date columns (From date and To date) eg. 1-Jan-07 to 31-Dec-07.

2. I also have other columns having month-yyyy. eg. March-07, Aug-08 etc.

3. I need to compare Mar-07, Aug-08 etc. with From and To date. If monh-yy falls within these dates, I should populate "1" in that cell.

## Number Of Full Months Between Dates

Jun 8, 2007

I want to calculate leave on the basis of Date of joining (DOJ). Rule is:
If DOJ is not he first day of the month, then no leave is earned for that month. Suppose DOJ is 26/12/2006, for Dec.2006 no leave is earned. One leave will be earned in January 2007 & subsequent months.

But if DOJ is 01/12/2006, then one leave will be earned in Dec.2006 & subsequent months also.

A zip file containg two excel files is attached for ready reference.

## Determine If 2 Dates Are Within Range Of Months

Jun 19, 2008

I have found the answer to several of my excel questions here in the past, but I'm completely stumped on this one.

I have two dates:
Jan-08
Sep-08

And I need to know if the range of these dates (January-September) falls between the range of two other dates:
Jan-08
Jun-08

Basically I'm checking to see if the range of months Entered falls in the First half of a year or the second, or both

## Conditional Formatting - Months

Jan 18, 2009

I'm trying to conditionally format 2 cells in a row based on when an ARP is due.

The data I'm working with looks a bit like: ...

## Conditional Formatting For +6 Months?

Aug 15, 2008

Ok, I'm going to use row 2 as an example.

If collumn AD row 2 = NI

Then I want collumn AE row 2 = Todays date + 6months (EG if todays date was the 18/08/08 then it would read 18/02/09)

And I want that for EVERY row except row 1

Is this possible with Conditional formatting, or do I have to fetch out the old VB code again?

Copyrights 2005-15 www.BigResource.com, All rights reserved