Calculating number of weeks from two dates
Hi, I'm both new here and to Excel generally!
I'm running Excel 2003.
I've designed a spreadsheet for my work to calculate holiday taken,
days left etc.
To make my job easier(!) I'm using (attempting to use!) formulae.
I can calculate days & hours taken for a given period but what I would
like to do is calculate the number of weeks from two dates (date leave
started to date leave ended.)
EG: week leave started is 03/01/05 week leave finished is 23/01/05 = 2
Is this possible?
-----------------------------------------------------------------...Lost "File Menu"
I accidentally grabbed the File menu (as in File / Edit / View / Insert /
Format...) in Excel 97 and dragged it. Now it's disappeared and I can't
figure out how to restore it. Can anyone help please?!?!
File>view>toolbars and customize, then click command tab, in the left pane
scroll down to built in menus, in the right hand pane grab a hold (with the
lef mouse button) of the word Edit and drag it to where you want it,
release the mouse button, close the customize box
"Nikki R" <Nikki R@discussions.microsoft.com> wrote i...Wrap Text and Blank Lines
I have a VBA script in Excel that fills data from one spreadsheet to
another. In the output file some of the cells are having the
formatting .WrapText = True. Now I have the strange situation that
some cells display half a line or an entire line of white spaces at
the end, even though in the text no blank or special characters are
I'd like to get rid of the white space or at least understand why this
is happening. I have looked at the size of the cells, the number of
characters but I have not yet found the trigger for the phenomenon.
I have been searching quite some time now and...Populate cells based on row selection
I'm having trouble figuring out how to populate cells based on
selecting a row. I basically have 5 columns with descriptive headers
with different values in each row. I would like to select a row and
have the 5 selected cells populate 5 main cells in another part of the
spreadsheet. Thus, of course those cell values will change everytime a
user selects a different row.
It's easy to use = and point to a particular cell (eg A3) but what if
that column letter changes all the time but the row number remains the
Use the =. Excel adjusts the formulas if you insert new rows or ...First and last used cell by row
I am trying to create a simple gant chart for which i would like to
enter the start date and the end date of a period of work.
I am using numbers in the rows to show the days of involvement and in
two columns i am showing the start and end dates which is shown as the
numbers 1-4 below:
1 2 3 4 Start End
Task 1 3 3 1 2
Task 2 1 1 1 2 4
Task 3 1 4 4
Could someone help me come up with a find first and last formula for
the respective cells?
On Sun, 22 Aug 2010 06:47:45 -0700 (PDT), ...How to subtract some rows from a range
I invoke the UsedRange and get the range as A14:B55 but I want to
subtract the first 5 rows from this used range how can I remove the
rows A14:B20 from the above range.
dim oUsed as range
FastExcel Version 2 now available.
"Simon Lenn" <firstname.lastname@example.org> wrote in message
> I invoke the UsedRange and g...previous row
I have 2 cols as follows A1 to A5 B1 TO B5
The names in the second column are the relationship between the cells
in A2 and A1, if a2 = a1 same,a2>a1 is OUT ,a2<a1 is IN.
I want to filter out the rows immediately prior to an IN row.
in the above example this would be the row containing A5.
There are many rows and columns in the sheet so doing it manually is
not an option
--------------------------------------------------------...How do I insert a photo into a specific cell?
I am attempting to insert photos into specific cells on an Excel spreadsheet.
I can insert photos just fine, but they are not anchored to any specific
cell. For example, I want a photo in cell A1 as part of a large spreadsheet
of photos and data. How do I do it?
On 5/13/2010 1:35 PM, Artemis Bob wrote:
> I am attempting to insert photos into specific cells on an Excel spreadsheet.
> I can insert photos just fine, but they are not anchored to any specific
> cell. For example, I want a photo in cell A1 as part of a large spreadsheet
> of photos and data. How do I d...Display one number in a cell even though 2 numbers get pasted into the same cell? #2
Ok. Maybe I didn't explain myself thoroughly.
I don't care about the second number. I just want to get rid of th
second number in the cell and only keep the first.
Can I do this by using Data - Seperate Text into columns? If so, ho
do I do this?
Any other possible suggestions?
DrSues02's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1256
View this thread: http://www.excelforum.com/showthread.php?threadid=26621
Select that column of cells.
data|text t...Your entry cannot be used. An integer or decimal number may be req
I have been trying to format cells and when I do, this message pops up:
"Your entry cannot be used. An integer or decimal number may be required." I
am also having problems with formulas I already had in place. Anytime I try
to change anything in a spread sheet this message pops up. It pops up in a
new spreadsheet or one I already have in place. Any ideas.
It sounds like there is a Data Validation constraint on the cell(s).
Select the cell, choose Validation from the Data menu, and see what
values are allowed You can either conform your entry to the constraint
or yo...How do I insert a dot leader in Excel?
I want to type a brief description in a cell and insert a dot leader to the
next cell which contains a money amount.
I think you will just have to do that manually like this:-
qtwt . . . . . . . . . .. . . . . .
I am not aware that there is any other way to do it.
If my comments have helped please hit Yes.
> I want to type a brief description in a cell and insert a dot leader to the
> next cell which contains a money amount.
right click the cell / Format Cell / Custom
in the box, enter @*.
now whatever you ...How to select and deselect rows in Excel #2
I have to Question regarding selecting and deselecting rows,columns,... in
1-When some row or cells are selected how we can we just deselect one row
or cell or column?We usually do it by keeping the Ctrl key in other office
2-Is there any way that we can use to select raws or columns one in
between?It could be very useful.
You can de-select a Worksheet, but not a Row nor Column.
You need to select the bulk portion of your Rows or Columns with the
select-shift/select than ADD additional Rows or Columns with the
Ctrl/select as required.
Also, do not ...how do i enter numbers starting with zero in excel
i am trying to enter account numbers into my worksheet but when i hit return
it removes the leading zeroes, how do i keep them.
FORMAT / CELLS / NUMBER / TEXT
> i am trying to enter account numbers into my worksheet but when i hit return
> it removes the leading zeroes, how do i keep them.
Type a leading apostrophe - makes it text, which is fine for numbers you're
not going to use in arithmetic calcs.
"achitsyerself" <achitsyerself@discussi...10 random numbers from 0 to 20
how can I write a method that returns me 10 random numbers from 0 to 20
(included), without repetitions?
Thanks a lot.
On 15-06-2010 12:10, Luigi wrote:
> how can I write a method that returns me 10 random numbers from 0 to 20=
> (included), without repetitions?
If you don't want repetitions they are not real random numbers.
You could get the number '15' 10 times in a row, it is possible, but=20
Med venlig hilsen / Best regards
S=C3=B8ren Reinke, IDCS #505926, TDI Trimix
Dive bl...Delete row problem
First 3 criteria work but not the 4th, which is looking cells in column "J"
with all start of "HG", anybody can help please?
Dim i As Long, LastRow As Long
Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = LastRow To 2 Step -1
If Cells(i, "I") = "" Or Cells(i, "J") = "sss" Or Cells(i, "J") =
"ccccc" Or Cells(i, "J") = "//" Or Cells(i, "J") = "HG*" Then
End...How to control number of fields in CSV saved from XLS?
We have a .xls file with 4 columns and hundreds of rows. The first row
has the field names as
value, info_1, info_2, info_3
The rest of the columns have value, info_1, info_2 populated, but not
When we save the .xls file as .csv, Comma delimited, the first few
records appear with 4 fields, the last being null, while the rest of
the fields appear with omly 3 fields.
We would ideally like to see 4 fields in all rows being written as
Can someone suggest what we should do?
Maybe fill those empty cells with a formula that looks empty:
>...Count Number of Cells in a Range
I know the best answer to this question is to use a pivot table, but
it's not my file - I'm just trying to help someone out here. :)
There is a table that lists a category from B6:B26 and the months from
C5:N5. Within B6:B26, there are four different possibilities (say A,
B, C and D).
What we need to do is write an equation that will tell us how many As,
Bs, Cs and Ds there are in each month, but only if the number in the
table is greater than zero.
I guess it would be something like a COUNTIFWHERE function, if that
were to exist. Any ideas?
Sorry if this is confusing...I'd ...Converting a calculated number into time
My columns are Completed Review, Hours Invested, Avg Time Per Review.
With 36 reviews completed and 65 hours invested, I divide to get
I know that the answer equates to about one hour and forty-eight
minutes - that's the number I need to see in my worksheet.
I appreciate any help you can offer. Have a great day!
KipHartman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31867
View this thread: http://www.excelforum.com/showthread.php?threadid=516319
...Change attributes of entire row when a particular string is found in row
I have a text file (csv-format) that is going to be opened by Excel
When the file is opened by excel, I would like to underline/highlight
the column headings above the data. (Much like the output of sql).
My logic looks like the following:
For entire page; do
find NEXT row where the first column has eg "Record id"
underline or highlight the ENTIRE ROW to grey
Can this be done via a macro or vb code ?
Can the macro/code be shipped within the csv text file ?
...How to find selected row?
I went through all properties searching for Selected or ActiveRow but I
cannot find it.
How exactly would you like this information? If you're talking about while
looking at the worksheet, take a look directly left of the formula bar in
the Name box. There you'll see the active cell's address. If you're
talking VBA, then you can use Activecell.row, Activecell.column or
Activecell.Address. Remember the first two will give you numeric returns
while the last example will be a string (address).
Hope this is what you're referring to, you were not...skipping rows with zero values
Here's my problem - Want to be able to skip rows with zero values and place next non-zero row in the one skipped.
many thanks jojo
Try using Autofilter, filter column B for 0 values, and
then delete those rows.
>Here's my problem - Want to be able to skip rows with
zero values and place next non-zero row in the one
>many thanks jojo
I would probably s...SQL query how to count rows in a group by
I want to count the rows of a query I'm running, but I'm already using
the count function with my group by. Here's my query:
select email, count(*) from customer group by email having count(email)
this just gives me every email and the email count, which is correct,
but I just want to know how many rows this gives, not the data passed
I'd like to
select count(email, count(*)) from customer group by email having count
On Jan 12, 2:40=A0pm, Cindy Lee <danparker...@yahoo.com> wrote:
> I want to count the rows of a query I'm ...Auto Numbering 09-24-04
I'm planning to implement MS CRM, and for all entities I want to keep the
prefix's as-is fot the auto numbering, but I would like to have the numbering
start with YYYY-001 each year so this year 2004-001, next year 2005-001 and
so on. Can this be possible from the client or is it possible to change this
feature in the database manually each year?
Hans Inge Letnes
The next sequence number is stored in the DB so you could definately "reset" it
each year after changing your prefix. Look at the organizationBase table for
MVP ...Adding ship-to phone number to SOP documents
How can I add the phone number from the ship-to address ID to SOP documents
in Report Writer? Thanks.
Nevermind - found it in the Sales Transaction Work table.
> How can I add the phone number from the ship-to address ID to SOP documents
> in Report Writer? Thanks.
i need to number my rows in a seperate column to count my inventory, but when
i filter my coulmns and do a custom filter my number column filters along
with the others even if the number column is not filtered???????????????
If I understand your question you want to see the visible rows numbered in
sequence? If so assume your data is in column A with titles on row 1, in the
column you want the row numbers to appear (say column B for this example),
enter the following formula in cell B2:
Copy this formula down.