Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date system
I am currently using excel 2003, and i understand that excel is able to
calculate dates by using a coding system. is it possible to set up a sort of active document, where by inputted dates that have expired would be flagged up in some way? for example; change colour of either the text or the cell itself? (if this makes no sense just give us a shout and i'll try and explain again haha!) thanks, Mike. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date system
Yes Mike, try conditional format
-- kk "mike" wrote: I am currently using excel 2003, and i understand that excel is able to calculate dates by using a coding system. is it possible to set up a sort of active document, where by inputted dates that have expired would be flagged up in some way? for example; change colour of either the text or the cell itself? (if this makes no sense just give us a shout and i'll try and explain again haha!) thanks, Mike. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date system
Hi Mike,
Yes on both counts. To find out more about how Excel handles dates just type date into the answer wizard in the help file. Also in the help file you can find out about conditional formatting. If you are having trouble sorting that out then post an example of your data and what you want it to do and someone here will show you the correct way to make those functions work for you. HTH Martin "mike" wrote in message ... I am currently using excel 2003, and i understand that excel is able to calculate dates by using a coding system. is it possible to set up a sort of active document, where by inputted dates that have expired would be flagged up in some way? for example; change colour of either the text or the cell itself? (if this makes no sense just give us a shout and i'll try and explain again haha!) thanks, Mike. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date system
cheers 'kk' did just the trick. easy when you know how eh? thanks again
"kk" wrote: Yes Mike, try conditional format -- kk "mike" wrote: I am currently using excel 2003, and i understand that excel is able to calculate dates by using a coding system. is it possible to set up a sort of active document, where by inputted dates that have expired would be flagged up in some way? for example; change colour of either the text or the cell itself? (if this makes no sense just give us a shout and i'll try and explain again haha!) thanks, Mike. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date system
i was a little hasty with the reply saying 'everything is ok'! i'm still
having trouble. the document i'm creating is just a very simple table with two columbs; one containing site addresses and the other containing a specific date which indicates when the maintenance on the property expires. all i need is for the cell to change when the end of maintenance date is reached. i understand how this would be done if i were using value (through conditional formatting) but not in relation to date. any help would be much appreciated. "MartinW" wrote: Hi Mike, Yes on both counts. To find out more about how Excel handles dates just type date into the answer wizard in the help file. Also in the help file you can find out about conditional formatting. If you are having trouble sorting that out then post an example of your data and what you want it to do and someone here will show you the correct way to make those functions work for you. HTH Martin "mike" wrote in message ... I am currently using excel 2003, and i understand that excel is able to calculate dates by using a coding system. is it possible to set up a sort of active document, where by inputted dates that have expired would be flagged up in some way? for example; change colour of either the text or the cell itself? (if this makes no sense just give us a shout and i'll try and explain again haha!) thanks, Mike. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date system
Hi Mike,
There are lots of variations that you can use for this sort of thing, but this might show you the basics. Say your dates are in B1:B100 Select B1:B100 Goto Conditional Formatting and set Formula is =TODAY()-B1<0 Set your formatting preferences and OK out. That will make future dates formatted and past dates unchanged .. Now change the formula to =TODAY()-B10 (remember to select all the cells B1:B100) That will make past dates formatted and future dates unchanged When you set the range before applying CF Excel automatically fills down the column. You can see this by clicking in say cell B53 you will see that the formula has updated to =TODAY()-B530 HTH Martin "mike" wrote in message ... i was a little hasty with the reply saying 'everything is ok'! i'm still having trouble. the document i'm creating is just a very simple table with two columbs; one containing site addresses and the other containing a specific date which indicates when the maintenance on the property expires. all i need is for the cell to change when the end of maintenance date is reached. i understand how this would be done if i were using value (through conditional formatting) but not in relation to date. any help would be much appreciated. "MartinW" wrote: Hi Mike, Yes on both counts. To find out more about how Excel handles dates just type date into the answer wizard in the help file. Also in the help file you can find out about conditional formatting. If you are having trouble sorting that out then post an example of your data and what you want it to do and someone here will show you the correct way to make those functions work for you. HTH Martin "mike" wrote in message ... I am currently using excel 2003, and i understand that excel is able to calculate dates by using a coding system. is it possible to set up a sort of active document, where by inputted dates that have expired would be flagged up in some way? for example; change colour of either the text or the cell itself? (if this makes no sense just give us a shout and i'll try and explain again haha!) thanks, Mike. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date system
Skipped a bit,
When you set the range before applying CF Excel automatically fills down the column. You can see this by clicking in say cell B53 you will see that the formula has updated to =TODAY()-B530 That paragraph should say When you set the range before applying CF Excel automatically fills down the column. You can see this by clicking in say cell B53 and going to FormatConditional Formatting you will see that the formula has updated to =TODAY()-B530 HTH Martin "MartinW" wrote in message ... Hi Mike, There are lots of variations that you can use for this sort of thing, but this might show you the basics. Say your dates are in B1:B100 Select B1:B100 Goto Conditional Formatting and set Formula is =TODAY()-B1<0 Set your formatting preferences and OK out. That will make future dates formatted and past dates unchanged . Now change the formula to =TODAY()-B10 (remember to select all the cells B1:B100) That will make past dates formatted and future dates unchanged When you set the range before applying CF Excel automatically fills down the column. You can see this by clicking in say cell B53 you will see that the formula has updated to =TODAY()-B530 HTH Martin "mike" wrote in message ... i was a little hasty with the reply saying 'everything is ok'! i'm still having trouble. the document i'm creating is just a very simple table with two columbs; one containing site addresses and the other containing a specific date which indicates when the maintenance on the property expires. all i need is for the cell to change when the end of maintenance date is reached. i understand how this would be done if i were using value (through conditional formatting) but not in relation to date. any help would be much appreciated. "MartinW" wrote: Hi Mike, Yes on both counts. To find out more about how Excel handles dates just type date into the answer wizard in the help file. Also in the help file you can find out about conditional formatting. If you are having trouble sorting that out then post an example of your data and what you want it to do and someone here will show you the correct way to make those functions work for you. HTH Martin "mike" wrote in message ... I am currently using excel 2003, and i understand that excel is able to calculate dates by using a coding system. is it possible to set up a sort of active document, where by inputted dates that have expired would be flagged up in some way? for example; change colour of either the text or the cell itself? (if this makes no sense just give us a shout and i'll try and explain again haha!) thanks, Mike. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date system
Massive help that martin, cracked it this time! thanks very much! i would
never have figured that out! haha cheers "MartinW" wrote: Skipped a bit, When you set the range before applying CF Excel automatically fills down the column. You can see this by clicking in say cell B53 you will see that the formula has updated to =TODAY()-B530 That paragraph should say When you set the range before applying CF Excel automatically fills down the column. You can see this by clicking in say cell B53 and going to FormatConditional Formatting you will see that the formula has updated to =TODAY()-B530 HTH Martin "MartinW" wrote in message ... Hi Mike, There are lots of variations that you can use for this sort of thing, but this might show you the basics. Say your dates are in B1:B100 Select B1:B100 Goto Conditional Formatting and set Formula is =TODAY()-B1<0 Set your formatting preferences and OK out. That will make future dates formatted and past dates unchanged . Now change the formula to =TODAY()-B10 (remember to select all the cells B1:B100) That will make past dates formatted and future dates unchanged When you set the range before applying CF Excel automatically fills down the column. You can see this by clicking in say cell B53 you will see that the formula has updated to =TODAY()-B530 HTH Martin "mike" wrote in message ... i was a little hasty with the reply saying 'everything is ok'! i'm still having trouble. the document i'm creating is just a very simple table with two columbs; one containing site addresses and the other containing a specific date which indicates when the maintenance on the property expires. all i need is for the cell to change when the end of maintenance date is reached. i understand how this would be done if i were using value (through conditional formatting) but not in relation to date. any help would be much appreciated. "MartinW" wrote: Hi Mike, Yes on both counts. To find out more about how Excel handles dates just type date into the answer wizard in the help file. Also in the help file you can find out about conditional formatting. If you are having trouble sorting that out then post an example of your data and what you want it to do and someone here will show you the correct way to make those functions work for you. HTH Martin "mike" wrote in message ... I am currently using excel 2003, and i understand that excel is able to calculate dates by using a coding system. is it possible to set up a sort of active document, where by inputted dates that have expired would be flagged up in some way? for example; change colour of either the text or the cell itself? (if this makes no sense just give us a shout and i'll try and explain again haha!) thanks, Mike. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date system
Glad to hear you got it working Mike.
Thanks for posting back. Martin "mike" wrote in message ... Massive help that martin, cracked it this time! thanks very much! i would never have figured that out! haha cheers "MartinW" wrote: Skipped a bit, When you set the range before applying CF Excel automatically fills down the column. You can see this by clicking in say cell B53 you will see that the formula has updated to =TODAY()-B530 That paragraph should say When you set the range before applying CF Excel automatically fills down the column. You can see this by clicking in say cell B53 and going to FormatConditional Formatting you will see that the formula has updated to =TODAY()-B530 HTH Martin "MartinW" wrote in message ... Hi Mike, There are lots of variations that you can use for this sort of thing, but this might show you the basics. Say your dates are in B1:B100 Select B1:B100 Goto Conditional Formatting and set Formula is =TODAY()-B1<0 Set your formatting preferences and OK out. That will make future dates formatted and past dates unchanged . Now change the formula to =TODAY()-B10 (remember to select all the cells B1:B100) That will make past dates formatted and future dates unchanged When you set the range before applying CF Excel automatically fills down the column. You can see this by clicking in say cell B53 you will see that the formula has updated to =TODAY()-B530 HTH Martin "mike" wrote in message ... i was a little hasty with the reply saying 'everything is ok'! i'm still having trouble. the document i'm creating is just a very simple table with two columbs; one containing site addresses and the other containing a specific date which indicates when the maintenance on the property expires. all i need is for the cell to change when the end of maintenance date is reached. i understand how this would be done if i were using value (through conditional formatting) but not in relation to date. any help would be much appreciated. "MartinW" wrote: Hi Mike, Yes on both counts. To find out more about how Excel handles dates just type date into the answer wizard in the help file. Also in the help file you can find out about conditional formatting. If you are having trouble sorting that out then post an example of your data and what you want it to do and someone here will show you the correct way to make those functions work for you. HTH Martin "mike" wrote in message ... I am currently using excel 2003, and i understand that excel is able to calculate dates by using a coding system. is it possible to set up a sort of active document, where by inputted dates that have expired would be flagged up in some way? for example; change colour of either the text or the cell itself? (if this makes no sense just give us a shout and i'll try and explain again haha!) thanks, Mike. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I get the system date & time for different cell record | Excel Discussion (Misc queries) | |||
Add System Date to Chart | Charts and Charting in Excel | |||
date system | Excel Discussion (Misc queries) | |||
Where is a sample formula for retrieving the system date? | Excel Discussion (Misc queries) | |||
What is the "system date" in Excel? | Excel Worksheet Functions |