Hot Dates

Hi,

I have a Year-at-a-Glance worksheet into which users enter important dates 
and associated actions to be taken.  I now want to use Conditional 
Formatting to highlight specific entries in the worksheet when their date 
matches today's date.

The problem I have is that I need to convert data from three independent 
cells into a date to compare it with 'TODAY()'.

  The first cell  (formatted as a number) carries the day, e.g.  2, 17, 23, 
etc.

  The second cell (formatted as General) carries the month, e.g. January, 
March, September, etc.

  The third cell (formatted as a number) carries the year, e.g. 2010, 2011, 
2012, etc.

Is it possible to concatenate the data from the three cells and covert the 
result to a valid date number to compare with 'TODAY()'?

If necessary, the second cell could carry the month as a number, e.g. 03, 
rather than as text, e.g. March.

Thanks,
-- 
Interloper


0
intruder (5)
8/6/2011 12:55:07 PM
excel 39879 articles. 2 followers. Follow

6 Replies
450 Views

Similar Articles

[PageSpeed] 4

Hi,

Am Sat, 6 Aug 2011 13:55:07 +0100 schrieb Interloper:

>   The first cell  (formatted as a number) carries the day, e.g.  2, 17, 23, 
> etc.
> 
>   The second cell (formatted as General) carries the month, e.g. January, 
> March, September, etc.
> 
>   The third cell (formatted as a number) carries the year, e.g. 2010, 2011, 
> 2012, etc.

it's easier if in the second cell the month is entered as number. 
Your year in C2, the month in B2 and the day in A2:
=DATE(C2,B2,A2)
If you have in B2 the name of the month:
=DATE(C2,MATCH(B2,{"January","February","March","April","May","June","July","August","September","October","November";"December"},0),A2)
 


Regards
Claus Busch
-- 
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
0
claus_busch (107)
8/6/2011 1:18:07 PM
With E1:G1 :
=DATEVALUE(E1&F1&G1)
Daniel


> Hi,
>
> Am Sat, 6 Aug 2011 13:55:07 +0100 schrieb Interloper:
>
>>   The first cell  (formatted as a number) carries the day, e.g.  2, 17, 23, 
>> etc.
>> 
>>   The second cell (formatted as General) carries the month, e.g. January, 
>> March, September, etc.
>> 
>>   The third cell (formatted as a number) carries the year, e.g. 2010, 2011, 
>> 2012, etc.
>
> it's easier if in the second cell the month is entered as number. 
> Your year in C2, the month in B2 and the day in A2:
> =DATE(C2,B2,A2)
> If you have in B2 the name of the month:
> =DATE(C2,MATCH(B2,{"January","February","March","April","May","June","July","August","September","October","November";"December"},0),A2)
>  
>
>
> Regards
> Claus Busch

-- 
Pour plus de facilit�, veuillez pr�ciser votre version d'Excel
Cordialement.
Daniel


0
8/6/2011 1:23:37 PM
"Interloper" <intruder@gmail.com> wrote:
> I have a Year-at-a-Glance worksheet into which users
> enter important dates and associated actions to be taken.
> I now want to use Conditional Formatting to highlight
> specific entries in the worksheet when their date matches today's date.
[....]
> The first cell  (formatted as a number) carries the day,
> e.g.  2, 17, 23, etc.
> The second cell (formatted as General) carries the month,
> e.g. January, March, September, etc.
> The third cell (formatted as a number) carries the year,
> e.g. 2010, 2011, 2012, etc.
>
> Is it possible to concatenate the data from the three
> cells and covert the result to a valid date number to
> compare with 'TODAY()'?

Yes.  Your conditional format in all 3 columns could be the formula:

=DATEVALUE($A1&$B1&$C1)=TODAY()

Note the use of partial-absolute references.  This permits you to assign the 
same conditional format to all 3 columns in one action.

However, I think it would be easier for your users as well as you if they 
entered the complete date in just one cell.  You can use Custom formatting 
to display each of the three cells the way you want.  For example:

A1:  the complete date, formatted as Custom d
B1:  =A1, formatted as Custom mmmm
C1:  =A1, formatted as Custom yyyy

The "complete date" can be entered in any form that Excel recognizes as a 
date.  In particular, the date can be entered the way that users would have 
entered across the 3 cells, e.g. 13 January 2012.

Then your conditional format in all 3 columns could be simply:

=$A1=TODAY()

or

A1:  =A1=TODAY()
B1:  =B1=TODAY()
C1:  =C1=TODAY()

whichever seems more intuitive to you.

Note:  If you assign the conditional format =A1=TODAY() to all 3 columns at 
the same time, Excel will change A1 to B1 and C1 automagically. 

0
joeu20041 (141)
8/6/2011 4:24:36 PM
Thanks for the helpful replies.  I'm certain I can now achieve the required 
highlighting of the cells when the dates match.

"joeu2004" suggested letting the user enter the entire date, but I think 
this may cause confusion. The Year-at-a-Glance worksheet has the year set in 
its header, whilst each of the 12 monthly sections have the month set in 
their headers. These cells are all locked and the user can only enter the 
day and the action to be taken for each specific month. The day cells are 
formatted as a 'number' and each cell has Validation set to allow only 
numbers between 1 and 28/29/30/31, as appropriate to the particular month.
-- 
Interloper 


0
intruder (5)
8/6/2011 8:55:37 PM
"Interloper" <intruder@gmail.com> wrote:
> "joeu2004" suggested letting the user enter the entire date,
> but I think this may cause confusion.
[....]
> the user can only enter the day and the action to be taken for each 
> specific month.

I concur, given these new facts.  Previously, you had said that "users enter 
important dates", and you went on to describe the 3 separate cells for day, 
month and year.  You said nothing of the fact that 2 of those cells are 
filled in automagically; so I ass-u-me-d that the user had to fill in all 3 
cells.  It is in that context that I suggested it would be easier for a user 
to fill in 1 cell instead of 3. 

0
joeu20041 (141)
8/6/2011 10:43:13 PM
"joeu2004" wrote
>
> I concur, given these new facts.  Previously, you had said that "users 
> enter important dates", and you went on to describe the 3 separate cells 
> for day, month and year.  You said nothing of the fact that 2 of those 
> cells are filled in automagically; so I ass-u-me-d that the user had to 
> fill in all 3 cells.  It is in that context that I suggested it would be 
> easier for a user to fill in 1 cell instead of 3.

Fair enough, and thanks for your input.

In order not to write an originating post that ran into several volumes, I 
pr�cised the information.

The fact is I intend to highlight the cells progressively, such that the 
cell changes colour when the action date is (say) 5 days away, and then 
changes colour again (to a more urgent colour combination of bold red text 
on yellow background) when the action date matches 'TODAY()'.  This will 
give users advance warning of an impending action and advise them that they 
need to get their act together within a few days  :-)

I can write the Conditional Formatting formulae for this; all I was stuck on 
was how to concatenate the three day/month/year cells into a meaningful 
date.

And now I know  ;-)

Thanks,
-- 
Interloper


0
intruder (5)
8/7/2011 12:22:59 PM
Reply:

Similar Artilces:

Date Conversion in Excel 2007
We've come across a strange problem in Excel 2007. We format a group (doesn't matter the number) of cells to have a Date format of xx/yy/zz (we've also tried other formats with the same result). After the cells are formatted, we type in 31401. This should convert the date to 03/14/01. This is what happened in older versions. Now, however, it's converting those dates improperly. What I've determined is that Excel is starting every date out as 01/01/1900. It then converts whatever we enter into a number, and adds that many days to 01/01/1900. For example, if I enter 01...

can i arrange a date colmn in order of the dates by some shortcut
please answer I'm not with you 'shortcut'. Can you not simply sort them? (Data>Sort...) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "sakshi" <sakshi@discussions.microsoft.com> wrote in message news:F46F10DE-8956-4F24-B783-DF10D21150A8@microsoft.com... > please answer ...

Schedule emails to automatically be sent at a future date and time without user being logged in.
We have a request to create an automated email to be sent out twice a month to our company pay statement notifications. The payroll department is currently using the option to Do not deliver before the certain date and time which is working for now but is a problem if she logs off of her machine or if the machine turns off on her overnight when the email is scheduled to be sent. I was wondering if there were any other options or solutions that we might be able to try to have a generic email setup to automatically send at certain dates and times without having the user being logged in...

Summing the number of incomplete jobs within a date range -sumprod
Hello, I need help figuring out a formula that counts the number of incomplete jobs for a specific worker that are within the next seven days. I would only want to count the "Date Completed" column if it was blank and within seven days from today's date. I've tried using sumproduct, but I can't seem to figure out how to get the date calculations to work properly. My data looks similar to this Worker Due Date Date Completed Bob 1/18/2010 1/13/2010 Bob 1/21/2010 Bob 2/20/2010 Tom ...

How to insert date using a pop up calendar control in a cell in E.
It has to be simple to add a control to an excel worksheet so when a user clicks on a cell a calendar pops up allowing the selection of a date. I just can't figure out how to do it. 1. View..ToolBars...Control Toolbox 2. Click the button for More controls (looks like two hammers) 3. From the list Select "Microsoft Date Time Picker " 4. Drag on sheet for the size of the control 5. Turn off design mode (green triangle on the control toolbox) to use the DT control "sinned81" wrote: > It has to be simple to add a control to an excel worksheet so when a user &...

HOT NEWS For Mac Users..........
YES ITS REALLY HEART BREAKING NEWS FOR EVERYONE http://polticsinfs.blogspot.com/ ...

Arrange deleted items by date deleted in Outlook 2002
Is there any way to set up a field in the "Deleted Items" folder that will allow you to sort by the date deleted? Thanks. DP My bad. Outlook 2000, not 2002. DP >-----Original Message----- >Is there any way to set up a field in the "Deleted Items" >folder that will allow you to sort by the date deleted? >Thanks. > >DP Um - the deleted items folder is for trash. Why do you keep trash beyond the end of your Outlook session? If you want to retain the mails prior to delete, create a folder for questionable items and move the mail there. Please realize...

Query Date Help
I need to create a query that will do the following: these are my fields: resign date, hire date I need to subtract the resign date from hire date but I want it to return the answer as years, months & days ie. 11/7/1986 - 6/18/1979 = 7 years 4 months & 19 days is there a way to use the DateDiff function to return years, monts & days? Look at this link on "A More Complete DateDiff Function" http://www.accessmvp.com/djsteele/Diff2Dates.html -- Good Luck BS"D "aldunford" wrote: > I need to create a query that will do the following: > > th...

Run query from date input on form
Hello all! I have what I hope is a easy situation. I have a table that has information that is entered daily with a field "Completed Date". I want to add a text box to a form where a user can enter a date and hit a button that will run a query for that specific completion date so I can have the query results exported to Excel. Would I use an unbound text box on the form? How would I set up the query to take the date entered in the form? Thank you very much! - Joker -- Message posted via http://www.accessmonster.com Yes, an unbound textbox would be one w...

Make Day CAPS In Custom Date Format
I've got a cell formatted thus: hh:mm ddd dd.mm.yy which returns 19:20 Thu 25.02.10. I want 19:20 THU 25.02.10, ie the day in caps. You need to use the Upper function, as in: =UPPER(TEXT(A1,"hh:mm ddd dd.mm.yy")) Regards, Fred "robzrob" <robzrob@hotmail.com> wrote in message news:15c33695-2b53-4d8f-8173-1aa6f178b739@g28g2000yqh.googlegroups.com... > I've got a cell formatted thus: hh:mm ddd dd.mm.yy which returns 19:20 > Thu 25.02.10. I want 19:20 THU 25.02.10, ie the day in caps. I don't think you'll be able to do it in for...

Polynomial Regression with Dates
I had Excel develop a 6th order polynomial regression with Dates (M/D/Y) as the independent variable (x) and Hydraulic Flow as the dependant variable (y). Unfortunately, when I try to use the equation to generate Hydraulic Flow values the equation outputs extremely large numbers that make no sense. I imagine that the problem has to do with the Date format. Does any one have a solution for this issue? Regards, OBR I wonder whether the problem is your use of the date -- not the format. Excel stores dates as an integer, starting with 1/1/1900. So April 14, 2008 is stored as 39552. (The...

My excel no longer displays the modification date when I hover on
Previously when I hovered my curser over a file when it is in the list mode, it would show the date it was last modified, now it just shows the type of file and the file size. Cannot be sure but it may have disapeared when I did the Office Update. How can I get it back ...

Sorting contact info by date
Hi Everyone: I'm hoping you can help. Is there any way that you can print a report that shows the last contact date for a Contact. We are trying make a list of customers not contacted within the last 3 months. Is there a way to export it to Excel for instance and create a report that way? Thanks! Assuming you already have a field populated with the last contact date for each contact: Create a table view that shows that field and any other fields you want to appear in the Excel spreadsheet. Set a filter on that view to show only those contacts with a last contact date greater t...

sheet names and representing a date
Hi I have a workbook that each sheet tab needs to be named the after a particular date in its worksheet. This is so that the contents can be imported into a database as they are updated. The dates have to be presented "yyyymmdd" with no dividers. Obviously it is a representation of a date and not the real thing. I am always getting errors because currently the sheets are being named manually with many mistakes. The sheet name comes from an actual date in cell E5 I have some vba to do it, but it is clumsy, using year month and day functions combined with conditional statements to al...

Date conversion
I am trying to separate dates and times in a spreadsheet through right and left formulas. After using these two formulas, Im getting my date answer in the format of 40,104, etc., and the time format in a similar format. I have tried changing the format on this and all other methods I can come up with, but nothing seems to work for me. Anybody have a suggestion on how to make the dates and times show up in their original format/ -- pete Assume that you are having the below value in A1 cell. A1 40,104 B1 cell =TEXT(A1,"DD/MM/YYYY") It will return the resu...

Sorting report by date range
I hae a query set up to sort all my data into nice little reports. The only problem is a month from now I don't want to have to print everything just to get yesterdays report. I would like a query that says only show the records for date X through Y, and I would like to use a form to enter the dates, run the query then show the report for printing. Thanks David Add 2 combo boxes to an unbound form (beginning of date range, and end of date range). Enter the following parameter in the date field of your query. Between [forms]![yourformname]![comboX] and [forms]![yourformname]![comboY...

Start Date Change from My Tasks
When a user modifies the start date in My Tasks and published to the PM, the correct dates show in the "change acceptance" window; however, the new date does not show correctly in the client task start date field. The gantt chart does show that the task has been delayed (with a dotted line) with the task starting at the new start date. The dotted line starts at the old start date. Changing finish dates appear to work correctly. Suggestions Sorry . . . running PS 2007 with June CU. "Jim Sipe" wrote: > When a user modifies the start date in My Task...

expired date
In Acess 2003 or 2007, is there a reasonably easy way to automatically send an e-mail or other alarm/message to come up when a date has expired on records pertaining to a form, perhaps on opening the form in question ? Any ideas gratefully received. David Hi, you can use docmd.sendobject, look at online help on this method for more info -- Best regards, ___________ Alex Dybenko (MVP) http://accessblog.net http://www.PointLtd.com "David" <David@discussions.microsoft.com> wrote in message news:E0D65CB9-2625-4B8D-98ED-CDBC421CA046@microsoft.com... &...

Date formatting issue
In my query I used: Month: Format(Month([DA Peer Review]![Start Time]),"mmmm") I created a query using the above formula so that I could add a month field in my query (i want to be able to generate reports by month) The current test data in my tables are all march dates and the start time field is build off of a command button using the Now formula. My new Month field as written above populated all with January. Since All of my test entries are dated with march dates, it should have returned March. I am wondering what I am doing wrong? Does it matter that my current star...

How to determine the what quarters are between 2 dates
Hi, I need to determine how many days fall between a start date and an end date. I am actually able to find that information, but how do I take that total number of days and determine how many days (of the total) fall within Q1, how many fall within Q2, how many fall within Q3, etc. For example if the total number of days between 2 dates is 243, then 66 working days fall in Q1, 66 days in Q2, 66 days in Q3 and 45 days in Q4. I need a function that can capture this information. Please advise. Thanks, MJ A bit unclear but perhaps sumproduct can help =sumproduct((a2:a200>startdate i...

Date Fill Series
We want to find an easy way to populate cells with the dates of our four-day Monday-Thursday work week for an entire year. For example, 8/263 8/24, 8/25, 8/26. The following week dates are 8/30, 8/31, 9/1, 9/2. I have been trying to use the fill series with no luck. Any help is greatly appreciated. If this is something you don't have to do a lot of, you could just go ahead and fill for the entire year.........then sort by Day of the week, and then delete the Fridays, Saturdays, Sundays.........then resort by date Vaya con Dios, Chuck, CABGx3 "S. Neese" <anonymous@di...

Calculation based with Range of Date
Hi I m facing problem with my worksheets which i have to calculate total sales in particular month. sheet 1 have Date Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST DD/MM/YY 1.10.2005 10000 2.10.2005 250000 3.10.2005 150000 4.10.2005 1000 1.11.2005 2.11.2005 3.12.2005 4.12.2005 1.1.2005 etc Sheet 2 have Quarterly Return Month Sale Undr 4% CST Sale Undr 10 CST Sale Undr 4% RST January February March In Sheet 2 i want to ...

date format in Excel #3
Hi All, What a wonderful mine of information this group is!!! Required daily reading to me. Thanks for the great contributions. Although I have managed to format a cell date (Excel in Office Pro 2000) with the "day" (e.g. "Friday" - as in dddd, dd mmm yyyy), I am stumped when it comes to putting a "th" or "nd" or "rd" (as in 1st/2nd/3rd etc...) Any ideas? TIA -- mac Take a look here: http://cpearson.com/excel/ordinal.htm In article <085A749B-889A-410F-B022-393684155179@microsoft.com>, "mac@bath" <barrydotjdotm...

Querying results for two recent dates
I have a table "DETAILS" in which fields are Name Id TestDate Grade (all fields can be duplicated) Now I want a query which should return me 'Name' 'Id' ' TestDate' 'Grade in 2nd Last Test' 'Grade in Latest Test' It should be noted that every person appears the test many times and i just want the recent two results. The query should return names of only those people who have appeared in either or both of the last two tests I was trying to do this by running a query on a query but the results were ...

Add expiration date to work orders and quotes
Anyone know of a way to add the expiration date to work orders and quptes? Thanks, Nick Use the variable Transaction.OrderDueDate Here is an example: <IF> <CONDITION> (Transaction.Type = TransactionLayaway) </CONDITION> <THEN> <ROW> "Expires: " Transaction.OrderDueDate "|" </ROW> </THEN> </IF> <IF> <CONDITION> (Transaction.Type = TransactionBackorder) </CONDITION> <THEN> ...