Can someone please tell me , is there a formular , for calculated hours worked
eg: i use one column for start times , one for finished times , and I would like the third column to caculate the hours inbetween , Start 9:00:00am - Finish 2:30:00pm - Hours Worked 5.5 - Any help would be gratefully recieved.
Use something like
=(End_Time - Start_Time)*24
If it is possible that the duration will cross midnight (e.g., start at
10PM,end at 2AM), use a formula like
=(End_Time - Start_Time + (Start_Time<End_Time))*24
Microsoft MVP - Excel
Pearson Software Co...conditional cell shading when a change occurs
Hi! Just recently joined this newsgroup, in hope that I can find an
answer to my problem. I am generally familiar with Excel, but need
some help on this particular problem.
I want to apply a format where whenever the number in a column differs
from the one previous to it, a cell shading color change is applied.
For example, you have a column of numbers in Excel:
5 (cell shading color change applied)
2 (cell shading color change applied)
3 (cell shading color change applied)
It doesn't matter what color is used, just so that it is applied
whenever there is a c...Cell format 12-16-09
I have some cells that have somehow become like they are frozen. They are not
protected but when right click or select 'Format'>'Cell' nothing happens and
I can't format those certain cells. Can someone tell me how to fix this
If the contents of the cell are text, then the various number options under
Format Cells will have no effect.
Use =ISTEXT(cellref) and =ISNUMBER(cellref) to tell you whether the contents
are text or number.
Is it just the number formats that you can't change? Can you change colour,
...Is there a way to protect just one cell, not the whole sheet?
The books I read seem to indicate so, but say you first must turn on the
'Protect Sheet' option. When I do that, everything is protected. Any
help much appreciated.
try the following:
- first select all cells you don't want to protect
- goto 'Format - Cells - Protection' and uncheck 'Locked'
- now protect your sheet
"Fred Exley" <firstname.lastname@example.org> schrieb im Newsbeitrag
> The books I read seem to indicate so, but say you first must turn on
> &...Sheet name in cell
I know it is possible as I've seen it done before, but can't remembe
how! I am looking to use the name of a worksheet (as it appears on th
tab) as the contents of a cell?
Stephen Pain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1526
View this thread: http://www.excelforum.com/showthread.php?threadid=31479
"Stephen Pain" wrote:
> Hi all,
> I know i...How do I link Excel chart axis scale settings to cells?
I want to link a chart's axis scale settings to cells. Is this possible?
You can not directly link the scales setting to cells.
Tushar has an addin which help.
> I want to link a chart's axis scale settings to cells. Is this possible?
Andy Pope, Microsoft MVP - Excel
Please ignore...I found the answer in another post.
> I want to link a chart's axis scale settings to cells. Is this possible?
...MSN BillPay: free service with 2004 & payment timing...
This is a multi-part message in MIME format.
Two questions from a new MSN BillPay user:
(1) I just bought Money 2004 Premium and it has 2yrs of free MSN =
BillPay. However, after I signed up, it shows I have zero free months. =
I've looked everywhere trying to find where you're supposed to say "I =
got money", but no luck.
(2) When I schedule to pay a bill, say one month from today, It gets =
entered into my register effective...Writing the Sheet Name to a Cell
Is there a formula that will display the Sheet name in a cell?
"Ruan" <email@example.com> wrote in message
> Is there a formula that will display the Sheet name in a cell?
Thanks Alan. That works great.
"Alan" <firstname.lastname@example.org> wrote in message
> "...Fill blank cells
How can I automatically fill blank cells with placeholders?
Select your range.
Type your placeholder value and hit ctrl-enter.
Debra Dalgleish explains a variation of this (with pictures!) at:
> How can I automatically fill blank cells with placeholders?
Thanks, it worked great.
"Dave Peterson" wrote:
> Select your range.
> Type your placeholder value and hit ctrl-enter.
> Debra Dalgleish explains a variation o...How to trap the change of color event in a cell?
In a Worksheet I have to trap the interior color change of an already
selected cell. The Worksheet_Change event does not work!
Is it possible? I'm using Excel2000
Unfortunately, there is no way to trap the change of a cells color.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"Barbara" <email@example.com> wrote in message
> In a Worksheet I have to trap the interior color change of an already
> selected cell. The Worksheet_Change eve...How to change cell tool tip
We are unable to work out how to chnage a tool tip. It is not a comment.
ie. a tool tip comes up when you click in a cell?
It might be a data validation input message.
Select the cell then goto Data>Validation. Select the Input Message tab.
"mike_mike" <firstname.lastname@example.org> wrote in message
> We are unable to work out how to chnage a tool tip. It is not a comment.
> ie. a tool tip comes up when you click in a cell?
...Can Out of Office be set to expire at a specific date and time?
I'm running Outlook 2003 on XP. I'd like to set my Out of Office message to
expire automatically. I can never remember to turn it off.
Also reply to email@example.com
herronm <firstname.lastname@example.org> wrote:
> I'm running Outlook 2003 on XP. I'd like to set my Out of Office
> message to expire automatically. I can never remember to turn it off.
I don't think this is possible. However, how can you not remember to turn
it off when it tells you it's on every time you start Outlook?
...Breaking out rows of data, sequentially, into headered columns
Merry Christmas everyone,
I highly respect the great minds that solve the many Excel riddles
that are posted on this newsgroup. I hope you are all having a great
If anyone is so inclined or feels like solving one on their return,
here is my issue...
I have existing code below that is quite useful for its former
purpose, but I wonder if it could be modified to suit a new
application. It probably just needs a little tinkering, but who knows.
1) My raw data consists of sequential (already sorted) numbers in
column A, running for unspecified (varying, that is) lengths of...This should not be so difficult but it is to me re: dates for medi
I"ve been working 2 solid days on trying to just figure out HOW to have two
date columns change the way I want them to. I KNOW it must be able to be
done in EXCEL but darned if I can figure it out. I have a medicine sheet
that has the date I last filled my prescription, then the date it can be
filled again. The time is 30 days from the first column of last filled.
THIS I think I have figured out how to do for the formula and tie it into the
computers calendar. BUT THEN I need that 2nd column to take it's new 30 days
back to the first column, so it now becomes the la...get the text string appear the most times !
Hi, all !
Sorry abt posting this question many time, but I think I gave a hard
understanding question before.
In my report, I need to get the value of a group of records that
appear the most times.
For example: I have a text "Sushi" appears in my report 10 times,
"Nobashi" appears 20 times, "shrimp" 5 times ---> my desire that is a
textbox show "Nobashi" as result.
Hope this way is easer to understand my problem.
Any suggestion for this ?
Thanks with appreciated of all ur time.
...changing the time between Home page refresh
We have had several customers wanting to change the automatic refresh time
for the home page. ( its currently 60 mins which isnt really in line with the
pace of todays businesses!)
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssoluti...Dates in Excel #8
Why does Excel assume that when you type December 2004 that you want
the numeric equivalent and not a text entry? Is this just another
illustration of Microsoft knowing what we need before we know it? Thanks
I don't know why MS assumes this but you can precede the entry with an
apostrophe and it will be text
"FJB" <FJB0623@aol.com> wrote in message
> Why does Excel assume that when you type December 2004 that you want
> the numeric equivalent and not a text entry? Is this jus...to CONCATENATE from the smallest date with at least 2 criteria
Here is the sample database.
Date Salesman Region
16-Aug-08 A N
16-Aug-09 B E
16-June-07 C S
15-Aug-07 A S
15-Apr-07 B E
4-Sep-07 D N
4-May-07 E N
6-Sep-07 A N
3-Oct-07 B W
24-Sep-07 E E
i would like to concatenate for Salesman B and Region E(east) in A1, so the
result will be "15-Apr-07,16-Aug-09"... which formula can s...Creating a Combined Date/Time Shortcut
I know Excel provides shortcuts for entering the date (CTRL + ;) and the =
time (CTRL + SHIFT + ;), but I really need one that will enter both the =
and time in the same cell at the same time. (Since Excel does provide=20
formatting for same-cell date and time, it seems kind of odd that there =
a shortcut to facilitate entry.) Is it possible to create an entirely =
that doesn't use macros? (This last point is important, since the =
shared on a network.)
Any help anyone can offer would be much appreciated--I'm getting=20
desperate. T...cell formula question
I am using the following formula in one of my cells: "=C1 / D2".
The problem with this formula is that the result will be "#DIV/0!"
if cell D2 contains no data.
Can I somehow fix my formula so it does NOT return any value
if either C1 or D2 is empty? I just want to avoid situations when
the outuput of my formula is "#DIV/0!" or "#Value#" because it
just doesn't look pretty to me, hahah!
On 18 Maj, 10:08, "Robert Crandal" <nob...@gmail.com> wrote:
> I ...Referencing Data Filter to a cell value
i am having continuing problems with the following:
I have a spreadsheet which lists project status. One of
the columns shows the installation subcontractor.
I want to have a drop down box where you select the
contractor, and once selected, the list automatically (or
by use of a macro button) filters to that selected. I
have put the drop down box in, then created a vlookup to
convert each contractor to a number, and placed a hidden
column with this info. Where i am stuck is how i get the
data filter to look at my link cell from the drop down
Any help is appreciated
R...Unable to Update Free/Busy #2
Outlook 2002 / XP
I know this appears to be a sproadic problem for many but I can't find
Error message - 'Unable to update public free/busy data.'
Trying not to gum up this ng, I have searched MS KB, tried to Google it,
various web sites, and this ng history but the solutions I have found do
not help. These include
using the ClearFreeBusy switch when opening Outlook (on opening, I
get the error)
Checking the Calendar Options|Free/Busy options (no check marks)
I found one reference to registry changes but no guidance
This problem did not occur unt...sorting dates
I have a list of dates (in a column of cells) in the form
16/07/2003, 20/07/2003, 08/08/2003. When I try to sort by
date, the dates are in the order of the day only, not
month then dates ie 08/08/2003, 16/07/2003, 20/07/2003.
How can I get around this so that the date are in the
Sounds like they are text, not true dates, which are numbers. Assuming your Windows regional
settings are dd/mm/yyyy, convert to dates this way:
- Edit/Copy a blank cell
- Select the column of dates
- Edit/Paste Special and select the Values and Add options
On Wed, 27 Aug 2003 0...How do I include cell contents from a form in generic statements/strings within the form?
I have a form that contains NAME in cell A:1, and GENDER in cell B:2.
I want to build a library of generic statements along the lines of:
NAME tries hard, but if HE/SHE tried harder HIS/HER results would be much
How can I make these statements get NAME from A:1, and derive HE or SHE and
HIS or HER from GENDER in B:2?
=A1&" tries hard, but if "&IF(B1="M","he","she")&" tried harder
"&IF(B1="M","his","her")&" results would be much better"
(there's no ...Day/Time Format
Can anyone help with this. I currently have code which lists class days and
times. Here is how it currently looks:
TuWThF, 9:00a-12:30p, 1:00p-2:30p, 9:00a-
12:30p, 1:00p-2:00p, 9:00a-12:30p, 1:00p-2:30p,
I want it to look like this:
Tu, 9:00a-12:30p, 1:00p-2:30p
W, 9:00a- 12:30p, 1:00p-2:00p
Th, 9:00a-12:30p, 1:00p-2:30p
F, 9:00a-12:30p, 1:00p-2:30p
Here is the code I'm using:
Public Function DaysTimes(SId As Variant) As String
Dim Days As Recordset 'days
Dim QStr As String
Dim sTime As String
Dim sDay As String
sTime = ...