ANmarS - Search results

Excel/VBA expert since Sep 1997 (20 years 9 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

Thu
May 13
2010

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


Answer

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.

VBAXLMan

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

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.


Answer

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.

VBAXLMan

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

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


Answer

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
="11-"&"4"
or even like this
="11-12"


VBAXLMan

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

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


Answer

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

VBAXLMan

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

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


Answer

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


VBAXLMan

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

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.


Answer

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


VBAXLMan

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

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
lahhak


Answer

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

VBAXLMan

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

Microsoft Office Excel 2007 counting months?

example
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()
Months
Days =B8-B7 = 592

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


Answer

Based on how you want to show the number of months (Integers or parts) you got several options:
1- do this in any cell
=(B8-B7)/30
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

VBAXLMan

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

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
http://officeblogs.net/excel/samradDatePicker.xla

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
Thanks


Answer

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
Fri
Jan 16
2009

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


Answer

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
So
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
Thu
Mar 30
2006

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

Excel


Answer

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

Tags

History