Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-entering the start and end of a month
Hi All
Can anybody give me pointers on how I can get 1 specific cell to auto-display the start of the current month, Laphan 1-11-03 and another cell to show the end, eg 30-11-03. I also want these cells to be editable so that the user can change the date if they want to something like 20-12-03. Any ideas?? Rgds Lagan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-entering the start and end of a month
Check out Chip's site Laphan
http://www.cpearson.com/excel/datetime.htm#DaysInMonth -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Laphan" wrote in message ... Hi All Can anybody give me pointers on how I can get 1 specific cell to auto-display the start of the current month, Laphan 1-11-03 and another cell to show the end, eg 30-11-03. I also want these cells to be editable so that the user can change the date if they want to something like 20-12-03. Any ideas?? Rgds Lagan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-entering the start and end of a month
Dear Ron
Many thanks for the info. This was perfect. Only thing that has confused me is how do I do the following: 1) In the example '=DATE(YEAR(A1),MONTH(A1),1)', its stating that I've entered a date in A1. How can I get it to look at the system date. For example, if the worksheet automatically grabbed today's date (11-11-03) then it could automatically get the first and last dates. 2) I know I want the above to grab the date automatically in an 'onLoad' state you might call it, but I also want to be able to allow the user to change these values should they need to. To give you background info, this Excel worksheet will go and extract info from an SQL DB within the date range that these 2 fields are going to specify. The Dutch user should run this report at the end of each month, but in an ideal world this can't always be the case so I want to be able to let them overwrite these dates with whatever date they want. Rgds Laphan Ron de Bruin wrote in message ... Check out Chip's site Laphan http://www.cpearson.com/excel/datetime.htm#DaysInMonth -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Laphan" wrote in message ... Hi All Can anybody give me pointers on how I can get 1 specific cell to auto-display the start of the current month, Laphan 1-11-03 and another cell to show the end, eg 30-11-03. I also want these cells to be editable so that the user can change the date if they want to something like 20-12-03. Any ideas?? Rgds Lagan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-entering the start and end of a month
Try this
1 + 2 ) Use Now like this If cell b1 is empty it use the system date If not the it use the date in b1 =DATE(YEAR(IF(B1="",NOW(),B1)),MONTH(IF(B1="",NOW( ),B1)),1) -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Laphan" wrote in message ... Dear Ron Many thanks for the info. This was perfect. Only thing that has confused me is how do I do the following: 1) In the example '=DATE(YEAR(A1),MONTH(A1),1)', its stating that I've entered a date in A1. How can I get it to look at the system date. For example, if the worksheet automatically grabbed today's date (11-11-03) then it could automatically get the first and last dates. 2) I know I want the above to grab the date automatically in an 'onLoad' state you might call it, but I also want to be able to allow the user to change these values should they need to. To give you background info, this Excel worksheet will go and extract info from an SQL DB within the date range that these 2 fields are going to specify. The Dutch user should run this report at the end of each month, but in an ideal world this can't always be the case so I want to be able to let them overwrite these dates with whatever date they want. Rgds Laphan Ron de Bruin wrote in message ... Check out Chip's site Laphan http://www.cpearson.com/excel/datetime.htm#DaysInMonth -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Laphan" wrote in message ... Hi All Can anybody give me pointers on how I can get 1 specific cell to auto-display the start of the current month, Laphan 1-11-03 and another cell to show the end, eg 30-11-03. I also want these cells to be editable so that the user can change the date if they want to something like 20-12-03. Any ideas?? Rgds Lagan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-entering the start and end of a month
Many thanks Ron
You're a star!!!! Rgds Laphan PS: noticed you're from the Netherlands - have you ever had to do any IntraStat reporting in Excel? Ron de Bruin wrote in message ... Try this 1 + 2 ) Use Now like this If cell b1 is empty it use the system date If not the it use the date in b1 =DATE(YEAR(IF(B1="",NOW(),B1)),MONTH(IF(B1="",NOW( ),B1)),1) -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Laphan" wrote in message ... Dear Ron Many thanks for the info. This was perfect. Only thing that has confused me is how do I do the following: 1) In the example '=DATE(YEAR(A1),MONTH(A1),1)', its stating that I've entered a date in A1. How can I get it to look at the system date. For example, if the worksheet automatically grabbed today's date (11-11-03) then it could automatically get the first and last dates. 2) I know I want the above to grab the date automatically in an 'onLoad' state you might call it, but I also want to be able to allow the user to change these values should they need to. To give you background info, this Excel worksheet will go and extract info from an SQL DB within the date range that these 2 fields are going to specify. The Dutch user should run this report at the end of each month, but in an ideal world this can't always be the case so I want to be able to let them overwrite these dates with whatever date they want. Rgds Laphan Ron de Bruin wrote in message ... Check out Chip's site Laphan http://www.cpearson.com/excel/datetime.htm#DaysInMonth -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Laphan" wrote in message ... Hi All Can anybody give me pointers on how I can get 1 specific cell to auto-display the start of the current month, Laphan 1-11-03 and another cell to show the end, eg 30-11-03. I also want these cells to be editable so that the user can change the date if they want to something like 20-12-03. Any ideas?? Rgds Lagan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of days in a specific month between various start and end d | Excel Worksheet Functions | |||
Month start placement on axes | Charts and Charting in Excel | |||
problem with entering month of the year | Excel Worksheet Functions | |||
how can I start a new line when entering data into a word wrapped. | Excel Discussion (Misc queries) | |||
Generating a blank worksheet at the start of the month | New Users to Excel |