+ Reply to Thread
Results 1 to 8 of 8

Conditional format cells that contain dates and times

  1. 08-18-2023, 04:55 AM #1
    omarahmedfazal
    omarahmedfazal is offline
    Registered User
    Join Date
    08-28-2018
    Location
    Colombo
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    4

    Conditional format cells that contain dates and times

    Hello All,

    I am trying to apply conditional formatting for the attached workbook.

    Basically, the correct format is as follows;

    24/07/2023 13:20
    25/07/2023 17:08
    26/07/2023 9:25
    31/07/2023 8:58
    31/07/2023 11:35

    and the incorrect format is as follows;

    25/7/2023 17:08
    26/0/2023 9:25
    31/07//2023 8:58
    31///07/2023 11:35

    I would like for Excel to highlight the cells when the incorrect format is shown but I cannot seem to figure it out through conditional formatting.

    I thought it would work but using the LEN formula gave me differences that seem confusing (See attached).

    Cheers!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-18-2023, 05:33 AM #2
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,293

    Re: Conditional format cells that contain dates and times 

    Please Login or Register  to view this content.
    try this formula
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong
    Reply With QuoteRegister To Reply
  3. 08-18-2023, 05:33 AM #3
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Conditional format cells that contain dates and times

    Use
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 08-18-2023, 05:39 AM #4
    omarahmedfazal
    omarahmedfazal is offline
    Registered User
    Join Date
    08-28-2018
    Location
    Colombo
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    4

    Re: Conditional format cells that contain dates and times

    Quote Originally Posted by popipipo View Post 
    Please Login or Register  to view this content.
    try this formula
    Below are the results. Did not work.

    03/08/2023 14:52 FALSE
    31/07/2023 16:12 FALSE
    01/08/2023 9:29 FALSE
    31/07/2023 10:20 FALSE
    31/07/2023 10:28 TRUE
    31/07/2023 10:36 FALSE
    Reply With QuoteRegister To Reply
  5. 08-18-2023, 05:39 AM #5
    omarahmedfazal
    omarahmedfazal is offline
    Registered User
    Join Date
    08-28-2018
    Location
    Colombo
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    4

    Re: Conditional format cells that contain dates and times

    Quote Originally Posted by TMS View Post
    Use
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Below are the results. Did not work.

    31/07/2023 16:12 FALSE
    01/08/2023 9:29 FALSE
    31/07/2023 10:20 FALSE
    31/07/2023 10:28 TRUE
    31/07/2023 10:36 FALSE
    Reply With QuoteRegister To Reply
  6. 08-18-2023, 05:56 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Conditional format cells that contain dates and times

    It DOES work:

    AliGW on MS365 Beta Channel (Windows 11) 64 bit

    A
    B
    C
    D
    E
    1
    		 Dates and Times
    LEN
    2
    24/07/2023 13:20
    45131.55556
    16
    FALSE
    3
    25/07/2023 17:08
    45132.71389
    16
    FALSE
    4
    26/07/2023 09:25
    45133.39236
    16
    FALSE
    5
    31/07/2023 08:58
    45138.37361
    16
    FALSE
    6
    31/07/2023 11:35
    45138.48264
    16
    FALSE
    7
    24/07/2023 13:14
    45131.55139
    16
    FALSE
    8
    31/07/2023 12:14
    45138.50972
    16
    FALSE
    9
    31/07/2023 12:36
    45138.525
    9
    FALSE
    10
    31/07/2023 13:37
    45138.56736
    16
    FALSE
    11
    31/07/2023 13:16
    45138.55278
    16
    FALSE
    12
    31/07/2023 09:39
    45138.40208
    16
    FALSE
    13
    03/08/2023 14:19
    45141.59653
    16
    FALSE
    14
    03/08/2023 14:52
    45141.61944
    16
    FALSE
    15
    31/07/2023 16:12
    45138.675
    9
    FALSE
    16
    01/08/2023 09:29
    45139.39514
    16
    FALSE
    17
    31/07/2023 10:20
    45138.43056
    16
    FALSE
    18
    31/07/2023 10:28
    31/07/2023 10:28
    16
    TRUE
    19
    31/07/2023 10:36
    45138.44167
    16
    FALSE
    20
    31/07/2023 12:19
    45138.51319
    16
    FALSE
    21
    31/07/2023 11:46
    45138.49028
    16
    FALSE
    22
    31/07/2023 11:59
    45138.49931
    16
    FALSE
    23
    31/07/2023 12:12
    45138.50833
    16
    FALSE
    24
    31/07/2023 12:28
    31/07/2023 12:28
    16
    TRUE
    25
    09/08/2023 18:21
    45147.76458
    16
    FALSE
    26
    27/07/2023 16:09
    45134.67292
    16
    FALSE
    27
    02/08/2023 11:49
    45140.49236
    16
    FALSE
    Sheet: Sheet1
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  7. 08-18-2023, 06:00 AM #7
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Conditional format cells that contain dates and times

    As Ali says, both formulae work. A "thank you for your help" would have been nice.
    Reply With QuoteRegister To Reply
  8. 08-18-2023, 06:01 AM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Conditional format cells that contain dates and times

    People forget that we are human beings and not chat bots, Trevor - a lack of common coursesy, sadly.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel Dates and Times in Number Format
    By doubleuson in forum Excel General
    Replies: 2
    Last Post: 11-10-2020, 10:03 AM
  2. [SOLVED] Conditional format cells with dates after 31/03/2018
    By A440 in forum Excel General
    Replies: 3
    Last Post: 04-12-2018, 12:51 PM
  3. [SOLVED] Conditional Format Calendar if Date Matches Range of Dates in Other Cells
    By blackburnsexcel in forum Excel General
    Replies: 7
    Last Post: 06-23-2016, 01:18 PM
  4. Convert string dates and times to different format
    By excel12121 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-22-2014, 02:39 PM
  5. How to conditional format cells with text and dates
    By alanb1976 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-04-2014, 04:32 AM
  6. Conditional Format Cell based on Dates in Other Cells
    By kortekn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-21-2013, 11:50 AM
  7. Conditional Format To Fill Cells Between Two Dates
    By demonfly100 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-04-2012, 07:56 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1