ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

11 Q & A posts tagged with DATE

May 13

What is the formula in Excel for a check box when clicked gives time and date in another cell?

I apologize if I am not explaining myself right.

I am working on a spreadsheet for tracking the work that's been done. I would like to use check boxes in a column and on another column for the current date and time when the box in a cell is selected. What is the formula and do you have an example to help me out, please?

For example the check box are in column V and I want the current time to be in the W column.

Thank you so much for helping!!!!


I can see what you are trying to do
The problem is that you can not (easily) do multiple checkboxes in a column and connect them to corresponding cells

I recommend you use the below shortcut keys:
CTRL + ;
To insert current date as constant
CTRL + Shift + ;
To insert current time in the selected cell as constant

This way, you will not have an updated version of Date or Time, means once you insert them ,they will not change.


Thursday 5/13/2010 8:59:04 PM
May 12

My excel is not working on my 2003 professional edition of microsoft. How do I get it to work again?

I just got rid of a virus and tried to upload microsoft updates. Now excel doesn't work. Every time I try to open it, it tells me that it is installing the new version. Then it says the network is unavailable and something about PRO11.MSI.


It is obvious, you will need to reinstall Office2003 again
You need to have the CD for it, the same original CD you installed it the first time, to do Installation repair.

When you insert the CD, you will have several options one of them is Reinstall Office to fix some errors.


Wednesday 5/12/2010 3:49:22 PM
May 7

How do I stop excel auto formatting cells into dates when I restart excel?

In other words I don't always want to have to go and change my settings back to what I want


That is one of Excel big bugs
Once you enter 1-4 or 11-12, Excel converts it into date

To go over that, you need to do one of the following:
1- Insert as many spaces as you can, between/after/before your number
2- Insert the single quote ' as the first character in that cell
3- Add some text before/after/between the numbers, may be like this G1-4
4- do a formula that generates exact number, like this
or even like this


Friday 5/7/2010 12:53:58 PM
May 3

Any microsoft excel experts here? need excel formula please help?

i know i asked this before but i never got a formula and i am still looking

i am looking for an excel formula in which i can add exactly one month to the month thats on the left of it on the previous row.

for example

Date Date
12/27/10 1/27/10

the second one i wish to show up automatically,

i know that it would be something like this:
=DATE(YEAR(C13), MONTH(C13)+1, DAY(C13))

but i am looking for a formula for the entire column so that i do not have to add the formula to each individual cell

thank you in advance


Just Copy and Paste that cell (with formula) to the cells below


Monday 5/3/2010 8:23:46 AM
Apr 25

How can i enter my country date in excel instead of english date?


You need to set the date in Control Panel to your country code to do that

Control Panel > Regional Options

Once you do this, it is automatically changed in Excel to your way


Saturday 4/25/2009 4:46:46 AM
Apr 23

Excel question - convert a date to text?

I have a date (example: 10/26/2008) that I want to change to text and return Oct - 08 (as text, not just formatted date field). Note: no day, just month and year.


Do as Chaminda suggests
Then copy these resultant cells
Paste them over the original cells but with "Paste Special" > Values

Do not use regular Paste command


Thursday 4/23/2009 7:37:14 PM
Apr 8

I received one excel Sheet from my friend,in that sheet?

he mentioned DATE in one cell,when i select that cell the TIME also appear in FORMULA BAR.but i cannot see TIME in the cell.Pls explain how to make the setting.

And also how to type in cell superscript or Power with the numbers.

thanks & regrads


The cell content is not all the time what shown on the cell output
That is called format
Meanning that the cell may not show everything in it
You will need to change the format of the cell

To do that, you need to select that cell, press CTRL+1 and choose another format from the list
I would recommend to go to Custom and do this
yyyy-mm-dd hh:mm

To do Superscript or Subscript, you need to do almost the same

- Select the text you want to superscript (not the cell only, but the text inside that cell)
- CTRL+1
- Font
- Superscript or subscript

Enjoy it


Wednesday 4/8/2009 8:57:31 AM
Apr 4

Microsoft Office Excel 2007 counting months?

how do i count the amount of months i have been employed
i want the worksheet to update automatical everytime i open it, no static values

Start Date =DATE(2007,8,22)
Today Date =TODAY()
Days =B8-B7 = 592

whats the formula to count the there a easy way
any help much appreciated...thanks


Based on how you want to show the number of months (Integers or parts) you got several options:
1- do this in any cell
This will give you how many 30 days this period has

2- You also do this
=INT(( B8-B7).30)
To get how many full months


Saturday 4/4/2009 1:34:45 PM
Jan 19

Display date and time together in a cell when using a DatePicker?

Hi, I added a date picker add-in from the following url. into excel

Ensured that i make a column accept dates. and now when i click on that cell i do get an icon to enter the date.
My question is the output comes as only date "01-mar-09" if i want the time also to be there along with date(in the same cell), what do i have to do.
I tried changing format in DATE in cell formating but its not working.
anyway i can get this done


It is a "DatePicker" and not a timepicker
That was 1.
2. what time do you want to use if the user didn't select one?
3. You can do the following:
Do a validation in the next cell with the expected time (as hours or halves) then allow the user to select from it

The date usually uses the integer part on the number to refer to the date, time uses the decimal part

So if your date is Jan-19-2009 (means=39832) then the time of that would be 12:00 AM

So when you select a date in DatePicker, you are actually selecting the hour 12:00AM in the morning of that date

To add a time, you will need to do a validation or do some other macro code

VBAXLMan is back to feed your Excel needs

Monday 1/19/2009 11:37:50 AM
Jan 16

How can retrieve the InstallDate for Windows and MS Office with an Excel Macro ?


The date of Windows installation is the date that Administrator account was created (or your User account)
And the date of Office istalled is the date of Microsoft Office folder was created
Depend on your Windows version (VISTA or XP) use this code to get the installation date/time of Windows

Sub WinDate()
Dim fs, fo
Set fs = Server.CreateObject("Scripting.FileSyste…
Set fo = fs.GetFolder("c:\Users\Administrator")
MsgBox fo.DateCreated
End Sub

Change the c:\Users\Administrator to c:\Documents and Settings\Administrator if you have WinXP
(Although the second one will run in VISTA as well)

Do the same for c:\Program Files\Microsoft Office

good luck

VBAXLMan is back

Friday 1/16/2009 3:59:00 AM
Mar 30

In excel,when I try to enter formula it is not applied on the cell.Pls suggest me?



Sometimes, Excel do autoformat to the result if one of the references are date or if the result can be converted to a date, so just go to Format--> Cells and make sure in the Number tab the Number value are selected from the list.

Thursday 3/30/2006 2:34:14 AM