Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Date range in Data Validation foiled by "Today()" function

Hi, I've set up a date range in Data Validation to restrict entries to a certain period.

In the date field, I've got "Today()" to default to today's date. Problem is, when "Today()" is after the "End date" setting, it doesn't trigger the error.

For example, with End Date set to 6 January 2016, the Today() function already in the spreadsheet won't trigger the error today (7 Jan 2016), but if i key in "1/7/2016" then the error is triggered.

Any ideas on how to have the error triggered by Today()?

thanks!
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Date range in Data Validation foiled by "Today()" function

Hi Pete,

Am Thu, 7 Jan 2016 05:11:05 -0800 (PST) schrieb pete:

Hi, I've set up a date range in Data Validation to restrict entries to a certain period.

In the date field, I've got "Today()" to default to today's date. Problem is, when "Today()" is after the "End date" setting, it doesn't trigger the error.

For example, with End Date set to 6 January 2016, the Today() function already in the spreadsheet won't trigger the error today (7 Jan 2016), but if i key in "1/7/2016" then the error is triggered.


insert today's date in the cell with CTRL+.
For the Data Validation in column A with end date in C1:
custom: =TODAY()*(A1<=$C$1)
or
Date: <= End date = C1


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Date range in Data Validation foiled by "Today()" function

On Thursday, January 7, 2016 at 5:31:57 AM UTC-8, Claus Busch wrote:
Hi Pete,

Am Thu, 7 Jan 2016 05:11:05 -0800 (PST) schrieb pete:

Hi, I've set up a date range in Data Validation to restrict entries to a certain period.

In the date field, I've got "Today()" to default to today's date. Problem is, when "Today()" is after the "End date" setting, it doesn't trigger the error.

For example, with End Date set to 6 January 2016, the Today() function already in the spreadsheet won't trigger the error today (7 Jan 2016), but if i key in "1/7/2016" then the error is triggered.


insert today's date in the cell with CTRL+.
For the Data Validation in column A with end date in C1:
custom: =TODAY()*(A1<=$C$1)
or
Date: <= End date = C1


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


Thanks Claus! I understood the two validation options, but not the guidance to "insert today's date in the cell with CTRL+."

is that "CTRL" plus "[period]"? or? and that would be in place of the "TODAY()" function?

thanks again!

pete
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Date range in Data Validation foiled by "Today()" function

On Thursday, January 7, 2016 at 5:31:57 AM UTC-8, Claus Busch wrote:
Hi Pete,

Am Thu, 7 Jan 2016 05:11:05 -0800 (PST) schrieb pete:

Hi, I've set up a date range in Data Validation to restrict entries
to a certain period.

In the date field, I've got "Today()" to default to today's date.
Problem is, when "Today()" is after the "End date" setting, it
doesn't trigger the error.

For example, with End Date set to 6 January 2016, the Today()
function already in the spreadsheet won't trigger the error today
(7 Jan 2016), but if i key in "1/7/2016" then the error is
triggered.


insert today's date in the cell with CTRL+.
For the Data Validation in column A with end date in C1:
custom: =TODAY()*(A1<=$C$1)
or
Date: <= End date = C1


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


Thanks Claus! I understood the two validation options, but not the
guidance to "insert today's date in the cell with CTRL+."

is that "CTRL" plus "[period]"? or? and that would be in place of
the "TODAY()" function?

thanks again!

pete


Press the semicolon key while holding down the Ctrl key!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Date range in Data Validation foiled by "Today()" function

Hi Garry,

Am Fri, 08 Jan 2016 01:14:45 -0500 schrieb GS:

Press the semicolon key while holding down the Ctrl key!


thank you for improving my mistake.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default Date range in Data Validation foiled by "Today()" function

On Friday, January 8, 2016 at 12:31:43 AM UTC-8, Claus Busch wrote:
Hi Garry,

Am Fri, 08 Jan 2016 01:14:45 -0500 schrieb GS:

Press the semicolon key while holding down the Ctrl key!


thank you for improving my mistake.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


Got it, thanks to both of you!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to prevent each item date from changing after "TODAY" formula Marvin Excel Worksheet Functions 2 June 3rd 09 08:27 PM
"NOW" or "TODAY" date function Chris Excel Worksheet Functions 2 April 19th 06 07:46 PM
How can I use the "TODAY ()" Function in an "IF/THEN" calculation Rodney Excel Worksheet Functions 4 April 12th 06 10:16 AM
Custom date format shows "051117.Thu" for today. Way to have "051117.Th", instead? StargateFan[_3_] Excel Programming 7 November 17th 05 11:21 PM
Validation, how to see "today" date on top of the drop down list? Hoshyar Excel Programming 8 October 18th 05 04:02 PM


All times are GMT +1. The time now is 04:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"