Excel recognizes a number as a date by its date format, which uses a slash (/) as a separator, for example, 1/25/2004 is recognized as a date by Excel.

**To change the default setting for the date format:**

1. From the Windows Start menu, select Settings, Control Panel, and then Regional and Language Options.

2. In Regional Options, select Customize.

3. Select the Date tab, and in the Date separator dropdown list, change the slash (/) to a dot (.).

4. Click Apply and then click OK.

**Screenshot // Changing the Default Date Separator from a Slash to a Dot When Entering a Date into a Cell**

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

"Somebody please tell me how to create a POP-UP DATE in EXcel.

Also, I need to know how can previous date/s automatically highlighted?"

"I am now using the formula you gave me in your previous reply, and it is working fine.

Thanks a lot for your help, "

"Sounds like you may already have it working, but the following might be useful from your note above:

=NOW()-MOD(MOD(B3,7)-1,7)"

I suffered a little until I discovered about this limitation. I've seen the answer about ths 1000 lines. Try numbering a blank sheet from 1 to 5000 on column A and apply tha Auto Filter to that Column and you see the limitations...

"I just tried using the formula, and i think i know a way that i can make it work, in my spreadsheet.

Thanks a lot for your help! "

"thanks for your reply,

So, if i was to use this formula, will it automatically change from 10/08/03 to 17/08/03, on that sunday?

Is there no formula that can be used, independant of any cell in the spreadsheet, i.e. you dont need to use ""A1"" or ""B3"", for example, in the formula.

Also, what does this formula mean:

=TODAY()+(1-WEEKDAY(TODAY(),1)) "

"To clarify, it will give you the current date in B3 if that date is already a Sunday.

In other words if you put in Sunday, 10 Aug 2003, it will return Sunday, 10 Aug 2003, not 3 Aug 2003. "

"If you enter any date in cell B3, then the following formula will give you the PRECEDING Sunday:

=B3-MOD(MOD(B3,7)-1,7) "

">>Reply:Date

Sidd Posted on: 31-12-1969

Alan,

Thanks for your reply.

What i actually wanted was, a formula that i can key into any random cell, which would then update every sunday as a new week commencing.

For example, the sunday gone was:

03/08/03,

so what i would like, is for it to update automatically on sunday coming to:

10/08/03

and then the same for every Sunday.

I hope this explanation is slightly better! "

"Not sure exactly what you are asking here, but to get the date for every Sunday, starting from, say, Sunday coming (10 August 2003) you could use the following:

A1: =VALUE(""10 August 2003"") =(37843)

A2: =A1+7 =(37850)

A3: =A2+7 (and so on...)

If you then format the cells appropriately, they will show days, dates, months, and years as you like.

*Yesterdays Date*

To get yesterday's date from todays:

=now()-1

Will give you a date / time exactly 24 hours prior to the current date / time.

Hope that helps, "

"I cannot be sure from your question exactly what format you already have your dates / months in. However, this is a fairly standard approach:

If you already have the current month somewhere (call that cell DATE), it will probably be a date number (e.g. you posted at (37839.4597222222) in Excel's date format or (37839+(11+2/60)/24) to be precise.

The month part of that is obviously August, and if you format a cell to only show the month, then you will see only August (or Aug or whatever you choose). However, the cell still contains the actual number).

To extract, say, ""Aug 2003"" from the number, use the following (remember that DATE is the cell where the date number is):

=text(DATE,""MMM YYYY"") (=""Aug 2003"")

To get ""Jul 2003"", use this:

=text(value(text(DATE,""MMM YYYY""))-1,""MMM YYYY"")

Hope that helps, "

"I run some end of month reports that work off a pivot table. It displays my info at a Yearly and Monthly breakdown. I am setting up my reports to feed off this Pivot Table. I need to know how to calculate what last month was i.e We are in August, I want it to only look at figures up until the end of July, so it needs to create a value i can use to create the cut off point. Monthly titles are: JAN, FEB, MAR etc... I was thinking a list i.e. JAN = 1 etc, but I can't figure out how to convert the current month into a value and subtract 1 to give me previous month.

Any one?"

"I did a search at Microsoft. There is a known limit of 1000 entries available when using the Autofilter feature. The web link is http://support.microsoft.com/default.aspx?scid=kb;en-us;295971

It gives a workaround, but it may or may not help, depending on the way you have your data organized."

David, I didn't know of any limitation with the Autofilter until yesterday. We also have a spreadsheet, containing 55464 rows, and have noticed that the Autofilter feature does not include all of the available distinct values in its picklist. It would seem that it is not just some fluke thing in our install here. Unfortunately, I haven't found any solutions or explanations yet, but am going to check the Microsoft website for any possibly noted errata. Version of Excel we are using is XP (2002).

plz send me database project in excel

"David, if you're importing data from say, SQL, and the query only limits pagesize to every 10,000 rows or something, you're gonna get those empty cells. try sorting all data by the default main column letters, not user created headings, and see if those unwanted cells pop up.

BTW, does anybody know of a newer Excel (perhaps 2k2?) that can handle MORE than 65,000-67,000 rows that Excel is currently limited to? no referrals to Access please. Thanks!"

David, I dont know of any such limitation. I use very large spreadsheets too and autofilter works fine. Check the contents of the cell for any spaces or other data that might be causing the problem Also, try your spreadsheet on another computer. If it wprks fine there, reinstall excel and it should work fine. Another thing - if your data is so extensive, it would be much better to use Access and design queries to return live data back to excel. That way you can work only on the data you need.

"When using autofilter for a very large spreadsheet, say 50,000 rows, it appears that the filter doesn't actually read down the entire file. For example, it can't find a number on row 45,000 but when you do a edit/find, that number is found.

Does anybody know of a row limitation with autofilter?"

THANK YOU VIKRAM... I WILL DO THAT!!

Meg, making the date absolute is one feature a lot of us are trying to achieve. I have personally been looking all over. I suggest you click on the following link and catch up on the progress so far. For the other part of your requirement i.e. the automatic entry of date into a cell if another is filled, use the ISBLANK() function.

how can i set up a spreadsheet to automatically calculate the week commencing date, eg. for every sunday. Also, how would i do the same for yesterday's date?

"1 Does anyone know how to make the User Form appear automatically when one opens the excel file.

2 How do I assign information entered from a User Form into appropriate worksheets in a work book."

I WOULD LIKE TO CREATE A WORKSHEET WHERE THE DATE AND TIME IS AUTOMATICALLY PUT INTO A CELL WHENEVER THE OPERATOR INPUTS INFORMATION INTO ANOTHER CELL... I.E. CELL A IS THE DATE OF INPUT, CELL B IS THE TIME OF INPUT.. CELL C IS WHERE THE OPERATOR INPUTS INFORMATION... BUT I DON'T WANT TO HAVE THE OPERATOR INPUT THE DATE/TIME - NOR DO I WANT IT REFRESHED EVERY TIME I OPEN THE FILE... ANY HELP???? THANKS IN ADVANCE!

Is there a way to assign an input mask to a cell or range to assist in entry especially with dates and times? Any help will be appreciated.

Just a word of thanks to Roy from Los Angeles. Just what I was looking for!!

I found this link that may be of help - it seems to be exactly what you are looking for.

Thanks for the replies, but I actually wants to know whether it is possible to display a proper calender box when clicking on a "date" cell and then select the date to be entered into that cell from the calendar box

Send me an e-mail with more details about what you want to do and I will try to help you.

How do I include a calendar to select the date from to enter into a cell?

or,=CONCATENATE(TEXT(A1,"mm/dd/yyyy")," ",TEXT(B1,"mm/dd/yyyy")).

=TEXT(A1,"mm/dd/yyyy")&","&TEXT(B1,"mm/dd/yyyy")

Anyone know how to concatenate dates so the result is MM/DD/YYYY, MM/DD/YYYY?

Anyone know how to display the last date saved in a cell?? Email Me please rsenior@lear.com

How do I calculate/formulate a cell to display the file save date?

Does anyone know how to show dates in uppercase in Excel?

The keyboard shortcut to enter todays date is Ctrl + ; ( semi-colon ), which enters the date into the cell, after which Enter should be pressed to complete the entry.

"Somebody please tell me how to create a POP-UP DATE in EXcel.

Also, I need to know how can previous date/s automatically highlighted?"

"Alan,

I am now using the formula you gave me in your previous reply, and it is working fine.

Thanks a lot for your help,

Sidd"

"Hi Sidd,

Sounds like you may already have it working, but the following might be useful from your note above:

=NOW()-MOD(MOD(B3,7)-1,7) "

I suffered a little until I discovered about this limitation. I've seen the answer about ths 1000 lines. Try numbering a blank sheet from 1 to 5000 on column A and apply tha Auto Filter to that Column and you see the limitations…

"Hi Alan,

thanks for your reply,

So, if i was to use this formula, will it automatically change from 10/08/03 to 17/08/03, on that sunday?

Is there no formula that can be used, independant of any cell in the spreadsheet, i.e. you dont need to use ""A1"" or ""B3"", for example, in the formula.

Also, what does this formula mean:

=TODAY()+(1-WEEKDAY(TODAY(),1))

Thanks for your help.

Sidd

"

"To clarify, it will give you the current date in B3 if that date is already a Sunday.

In other words if you put in Sunday, 10 Aug 2003, it will return Sunday, 10 Aug 2003, not 3 Aug 2003.

Alan."

"Hi Sidd,

If you enter any date in cell B3, then the following formula will give you the PRECEDING Sunday:

=B3-MOD(MOD(B3,7)-1,7)

Hope that helps,

Alan."

"Alan,

Thanks for your reply.

What i actually wanted was, a formula that i can key into any random cell, which would then update every sunday as a new week commencing.

For example, the sunday gone was:

03/08/03,

so what i would like, is for it to update automatically on sunday coming to:

10/08/03

and then the same for every Sunday.

I hope this explanation is slightly better!

Thanks

Sidd"

"Sidd,

Not sure exactly what you are asking here, but to get the date for every Sunday, starting from, say, Sunday coming (10 August 2003) you could use the following:

A1: =VALUE(""10 August 2003"") =(37843)

A2: =A1+7 =(37850)

A3: =A2+7 (and so on...)

If you then format the cells appropriately, they will show days, dates, months, and years as you like.

*Yesterdays Date*

To get yesterday's date from todays:

=now()-1

Will give you a date / time exactly 24 hours prior to the current date / time.

Hope that helps,

Alan. "

"Hi Gareth,

I cannot be sure from your question exactly what format you already have your dates / months in. However, this is a fairly standard approach:If you already have the current month somewhere (call that cell DATE), it will probably be a date number (e.g. you posted at (37839.4597222222) in Excel's date format or (37839+(11+2/60)/24) to be precise.The month part of that is obviously August, and if you format a cell to only show the month, then you will see only August (or Aug or whatever you choose). However, the cell still contains the actual number).

To extract, say, ""Aug 2003"" from the number, use the following (remember that DATE is the cell where the date number is):

=text(DATE,""MMM YYYY"") (=""Aug 2003"")

To get ""Jul 2003"", use this:

=text(value(text(DATE,""MMM YYYY""))-1,""MMM YYYY"")

Hope that helps,

Alan. "

I run some end of month reports that work off a pivot table. It displays my info at a Yearly and Monthly breakdown. I am setting up my reports to feed off this Pivot Table. I need to know how to calculate what last month was i.e We are in August, I want it to only look at figures up until the end of July, so it needs to create a value i can use to create the cut off point. Monthly titles are: JAN, FEB, MAR etc... I was thinking a list i.e. JAN = 1 etc, but I can't figure out how to convert the current month into a value and subtract 1 to give me previous month. Any one?

"David,I did a search at Microsoft. There is a known limit of 1000 entries available when using the Autofilter feature. The web link is http://support.microsoft.com/default.aspx?scid=kb;en-us;295971

It gives a workaround, but it may or may not help, depending on the way you have your data organized."

David, I didn't know of any limitation with the Autofilter until yesterday. We also have a spreadsheet, containing 55464 rows, and have noticed that the Autofilter feature does not include all of the available distinct values in its picklist. It would seem that it is not just some fluke thing in our install here. Unfortunately, I haven't found any solutions or explanations yet, but am going to check the Microsoft website for any possibly noted errata. Version of Excel we are using is XP (2002).

hi plz send me database project in excel

David, if you're importing data from say, SQL, and the query only limits pagesize to every 10,000 rows or something, you're gonna get those empty cells. try sorting all data by the default main column letters, not user created headings, and see if those unwanted cells pop up.BTW, does anybody know of a newer Excel (perhaps 2k2?) that can handle MORE than 65,000-67,000 rows that Excel is currently limited to? no referrals to Access please. Thanks!

David, I dont know of any such limitation. I use very large spreadsheets too and autofilter works fine. Check the contents of the cell for any spaces or other data that might be causing the problem Also, try your spreadsheet on another computer. If it wprks fine there, reinstall excel and it should work fine. Another thing - if your data is so extensive, it would be much better to use Access and design queries to return live data back to excel. That way you can work only on the data you need.

When using autofilter for a very large spreadsheet, say 50,000 rows, it appears that the filter doesn't actually read down the entire file. For example, it can't find a number on row 45,000 but when you do a edit/find, that number is found. Does anybody know of a row limitation with autofilter?

how can i set up a spreadsheet to automatically calculate the week commencing date, eg. for every sunday. Also, how would i do the same for yesterday's date?

"1 Does anyone know how to make the User Form appear automatically when one opens the excel file.

2 How do I assign information entered from a User Form into appropriate worksheets in a work book."

I WOULD LIKE TO CREATE A WORKSHEET WHERE THE DATE AND TIME IS AUTOMATICALLY PUT INTO A CELL WHENEVER THE OPERATOR INPUTS INFORMATION INTO ANOTHER CELL... I.E. CELL A IS THE DATE OF INPUT, CELL B IS THE TIME OF INPUT.. CELL C IS WHERE THE OPERATOR INPUTS INFORMATION... BUT I DON'T WANT TO HAVE THE OPERATOR INPUT THE DATE/TIME - NOR DO I WANT IT REFRESHED EVERY TIME I OPEN THE FILE... ANY HELP???? THANKS IN ADVANCE!

Is there a way to assign an input mask to a cell or range to assist in entry especially with dates and times? Any help will be appreciated. Thanks.

Just a word of thanks to Roy from Los Angeles. Just what I was looking for!!

I found this link that may be of help - it seems to be exactly what you are looking for.

Thanks for the replies, but I actually wants to know whether it is possible to display a proper calender box when clicking on a "date" cell and then select the date to be entered into that cell from the calendar box

How do I include a calendar to select the date from to enter into a cell?

Anyone know how to concatenate dates so the result is MM/DD/YYYY, MM/DD/YYYY?

Anyone know how to display the last date saved in a cell?? Email Me please rsenior@lear.com

How do I calculate/formulate a cell to display the file save date?

"Does anyone know how to show dates in uppercase in Excel?

Thanks

Ian"

The keyboard shortcut to enter todays date is Ctrl + ; ( semi-colon ), which enters the date into the cell, after which Enter should be pressed to complete the entry.