» Changing the Default Date Separator from a Slash to a Dot When Entering a Date into a Cell

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.

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

Thanks
Ian

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

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

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

Please write to vissza@yahoo.com

Thanks!

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

or

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

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

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

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

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

Pop-up Calendar for Excel

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

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.

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!

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.

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?

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.

http://www.mrexcel.com/board2/viewtopic.php?p=264053#264053

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

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?

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.

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!

hi
plz send me database project in excel

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).

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.

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?

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.

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.

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

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.

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 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

Alan,

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!

Sidd

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 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)

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

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?

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?