+ Reply to Thread
Results 1 to 5 of 5

conditional formatting, dates calculation

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-11-2013
    Location
    St Moritz, GR, Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    187

    conditional formatting, dates calculation

    O and P are formula generated dates from AD and AE in another spreadsheet using

    =IF('Contract activity'!AE2=0,"",TEXT('Contract activity'!AE2,"d mmm yy")) and
    =IF('Contract activity'!AH2=0,"",TEXT(('Contract activity'!AH2),"d mmm yy"))

    I want to highlight when P comes a day after O, so I have conditional formatting for

    =ROUNDDOWN('Contract activity'!AH2,0)=(ROUNDDOWN('Contract activity'!AE2,0)+1)

    Normally it works but not always. 30 June 2013 to 1 July 2013 doesn't highlight. Neither does 29 Nov 2014 to 30 Nov 2014. Why?

  2. #2
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: conditional formatting, dates calculation

    I've tried using the same conditional format and it seems to work fine for the dates listed. Maybe attach a sample spreadsheet?

  3. #3
    Forum Contributor
    Join Date
    12-11-2013
    Location
    St Moritz, GR, Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: conditional formatting, dates calculation

    Voila. :-)
    Attached Files Attached Files
    Last edited by johnandrews; 01-02-2014 at 05:06 AM.

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: conditional formatting, dates calculation

    hi there again john. a few things to ask/note:

    1. your column O & P are referring to the columns A & B in the file i suppose?

    2. if you merely want to change the format of a date, just format cells instead of doing a TEXT formula. reason is because Excel recognises dates as integers & time as decimals. to verify, type in 1jan2013 in A1. and 12 pm in B1. Format both cells to General. you will see that A1 is 41275 (meaning 41,275th day from 1 Jan 1900) & B1 is 0.5 (1/2 a day).* so it's better to have it remain as a number. so in A2:
    =IF('Contract activity'!AD2=0,"",'Contract activity'!AD2)
    format cells -> custom:
    d mmm yy

    3. i don't see dates of 30 June 2013 & 29 Nov 2014

    4. i guess you are concerned about the time in the cells, you could use INT (integer since you now know dates are integers). ROUNDDOWN definitely works too, but just suggesting something shorter. don't need the brackets for the 2nd part too
    =INT('Contract activity'!AH2)=INT('Contract activity'!AE2)+1

    Edit:
    5. do NOT use whole columns for conditional formatting. your Excel would crash someday
    Last edited by benishiryo; 01-02-2014 at 05:19 AM.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Forum Contributor
    Join Date
    12-11-2013
    Location
    St Moritz, GR, Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: conditional formatting, dates calculation

    I removed some data for confidentiality but rows 1, 17, 25, 28, 40, 42, and 50 should all be green. Otherwise conditional formatting and info in A-B all reads direct from worksheet 'Contract Activity' to simplify the numbers.

    Could I also do this by creating a column in between? The original data is on 'Contract Activity'. I could make another column with =INT(AH2), and have my conditional formatting refer to this instead?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting with approaching due dates and completed dates
    By rogernation in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-13-2013, 04:12 PM
  2. Replies: 4
    Last Post: 08-29-2013, 11:23 AM
  3. [SOLVED] Conditional Formatting of: dates within 1 month and dates before today.
    By Luke Smith in forum Excel General
    Replies: 5
    Last Post: 06-18-2013, 07:29 AM
  4. [SOLVED] Conditional Formatting of a calculation of adjacent cells
    By JimDandy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2013, 06:56 PM
  5. Conditional Formatting Calculation Issue
    By Marchey in forum Excel General
    Replies: 4
    Last Post: 10-19-2012, 09:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1