Problems with date calculations (bank hols etc)
I have a database of dates of claims received.
Column A has the customers name, Column B is the date claim was received.
Problem is that in column C i want to enter the 60th day (i.e. Whatever is
in column B + 60days - however this MUST NOT include Saturdays or Sundays in
the calculation and should also NOT include bank holidays). I have a
separate sheet of all the bank holidays listed. I haven't a clue how to
solve this one !. Please help. So for e.g Mr Smith puts his claim in on 16th
March 2005, i want column C to add 60 WORKING days onto this, but miss out
the bank holidays (i.e. 25/4...Custom Format time to arc
I'm trying to find a symbol to Format/Cells/Custom, a symbol that
represents the max value of 360�. I know "H" is equal to the max value
of 24 hours, "m" is equal to sixty minuets and "s" is equal to sixty
seconds, this [h] will roll hours over past 24, and I know to format
cells like this h� m' s.00\" but these symbols represent time not the
arc of 360�. I want to convert arc to time and time to arc with the
symbols in costum format, I don't think this is possible, is it? Am I
doing this backwards? Should I be working with a formula or a
combina...Remove space and trailing text from number
Using xl xp pro.
Receive text file wish to convert to xl
Some data comes in like this:
Would like to trim " oz" w/o quotes
So all I'm left with is 12
Here's my formula:
However other data comes in this format:
How could I re-work my formula to take care of either condition
Remaining value beginning from left of string could be any number of digits
If col a contains text I receive #value! error
I take care of this with
But it would be more el...A Date Query
I need to create a formula which can do the following:
IF the end date (S2) is greater than than 12/31/05, AND the start date
(R2) is less than 12/31/05 then...(and I know the formula for here).
Any ideas? Thanks?
SamuelT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27501
View this thread: http://www.excelforum.com/showthread.php?threadid=475036
=IF(AND(S2>DATE(2005,12,31),R2<DATE(2005,12,31)),IF TRUE,IF FALSE)
-------...Conditional Formatting 03-29-07
I have been playing around with conditional formatting. I Have a few text
boxes with shading if certain items are entered. Now I want the first 3
fields in my subforms to be shaded if a record is started. I can get the
shading to work but I have the next record showing below the current record
that is entered. Those fields are also shaded, I only want them to shade if
there is anything entered in any of those 3 fields. Is there a wild card to
use so if anything is entered so the other 3 fields will shade?
I am not 100% sure I understand what y...Formating Bars based on the value of a bar
I have created a bar chart in an access report. I want the bars to change
color based on the value of the specific bar. For example, if the value of a
bar is less than 1, it is red, if it is between 1 and 5, it is yellow, if it
is greater than 5 it is green.
How can I do this?
Seems like an easy problem, but I've just spent a couple of hours playing
with this and I can't make it work. The problem is that the Value property
of the points in the SeriesCollection is not exposed in the VBA interface.
It certainly exists (eg. with the chart in design mode, if you hover the
mouse...How to put Time or Date Only in Cell 1) Time HHMMSS 2) Date YYYYMMDD
I need to have a cell = the current time in the format HHMMSS
And when I export as Comma Delimited or Fixed Length Ascii, I need for it to
remain in that format.
Ditto for Date as YYYYMMDD
I've played around with both =TIME and =DATE but no luck.
Can anyone tell me how to do this?
AND, once the forumula is in the cell... how can I get it to update to the
current time or date?
thanks for any help
I formatted my times as hhmmss and my dates as yyyymmdd and then did a
I chose the comma separated values (*.csv) and I got what you wanted.
I opened it in notepad to ver...Aligning text to right margin using right tab
I am trying to create a heading in a Publisher 2003 document, the first part
of which starts flush with the left margin and the second part of which ends
flush with the right margin. I have done this before in Word using left
aligned text, with a right tab stop at the right margin. However, this
doesn't seem to work in Publisher - the tab seemingly has to be in indented
from the actual margin position, otherwise the text wraps onto the next line.
Am I doing something wrong, or have I missed something? I don't really want
to create a separate text box to achieve this, as ...BSOD in RtlStringCbVPrintfA() when using %wZ format specifiers on XPSP3
I get a BSOD when using this func with the UNICODE_STRING format
specifier %wZ (the bug is actually in vsnprintf. when the
UNICODE_STRING is empty. ie, a valid buffer (zeroed or unzeroed makes
no difference) with the Length and MaximumLength set to zero.
Now I know these specifiers aren't documented, but they exist, and are
well known, and used.
This is the crash:
0: kd> !analyze -v
****************...how to store text in a cedit window to file
how do you store the text from a Cedit window, or any CString, to file as
text that can be viewable by other applications such as notepad?
if(!f.Open(_T("Name.of.your.choice.txt"), CFile::modeCreate | CFile::modeWrite)))
return FALSE; // or other appropriate error action
return TRUE; // or other appropriate success action
On Mon, 4 Aug 2008 16:33:30 -0700, "Nick Schultz" <email@example.com> wrote:
>how do you store the text from a Cedit window, or any C...Report Card-like format
I don't know the best NG for this questions, so I guess
that's why they've got excel.misc <g>.
I have a simple table of data like this:
Date Field1 Field2 Field3
7/14/2003 123 254 75
7/15/2003 201 10 124
7/16/2003 215 258 2
7/17/2003 78 8 89
7/18/2003 125 186 582
7/19/2003 254 45 257
7/20/2003 200 199 89
The table is always the same size, but the data changes as
the days go by.
What I'd like to do is have the table look like a card.
Sort of like a report card or other. My idea is a
rectangle ...Cells formated as text do not always display properly
I am using Office XP and Windows XP. I have formatted a column of cells as
text and word wrap is checked, horizontal alignment is Left, Vertical
alignment is Bottom. I use these cells to type a short narrative. The
problem is some of the cells will not display properly, all they show is
######. The row height will expand to hold the text sometimes but not always
but it will still only show ######## instead of the text I typed. If I change
the cell type to General all the text is visible. This does not happed to
evey cell, only some of them. I can type several rows of data with a
narr...Concatenate text & number cell
I need to concatenate a word and a number cell. eg,
Concatenate("Limit = $",A1) where A1 = $100,000. It shows
up like this: "Limit = $100000". However I want the
number to format with the comma between the thousand,
ie. "Limit = $100,000"
Does any one know how to do this. Thanks in advance.
="Limit = " & TEXT(A1,"$#,##0")
In article <firstname.lastname@example.org>,
"John" <email@example.com> wrote:
> Hi all,
> I need to concatenate a wo...convert number into corrseponding text
I would like to know if there is a way to display text on the corresponding
I type 530,000
and it would show me Five hundred and thirty thousand
Take a look at http://www.xldynamic.com/source/xld.xlFAQ0004.html
(remove nothere from the email address if mailing direct)
"mustafa" <firstname.lastname@example.org> wrote in message
> I would like to know if there is a way to display text on the
> I type 530,000
> and it ...Text Box 05-21-10
I have the following event procedure on a text box:
Private Sub txtNotesOnTransaction_BeforeUpdate(Cancel As Integer)
Debug.Print "User: " & CurrentUser() & " " & Me!txtNotesOnTransaction
It is suppose to stamp the user name in the text box but it does not work.
The debug.print will just display (in the immediate window of the code pane)
the name and the Notes. If you want the notes saved with the name, do this:
Me!txtNotesOnTransaction = "User: " & CurrentUser() & " " &a...Compare dates (one cell not in date format)
I have one column of cells in date format (6/14/2005) and anothe
column of cells which also contains a date pulled from a database bu
this second column is not in date format, it comes from the databas
like this 2005Jan20. I need to compare the two dates to work out th
number of days between the dates but I can't convert the second date t
Is there any way I can work out the number of days between the tw
Thanks in advance
craigcsb's Profile: http://www.excelforum.com/...Excel Date
want to be able to have a cell auto fill the first time the sheet i
saved so i have the creation date. Then if poss - never let them chang
the cell so it keeps record of when the file was first created.
Thanks - any help will get some free chocolate! ;-)
Astor Chocolate Corp.
Message posted from http://www.ExcelForum.com
Try some code in the workbook module ; e.g.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
If Range("Sheet1!A1") = "" Then
Range("Sheet1!A1") = Date
Then set Sheet1...Convert text to number
I receive a workbook that contains unformatted text and need to wor
with numbers. I have MS Excel 2000 and can not convert text to number
Just do not know how.
P.S. Select Range - Format to number does not work.
Thanks a lot to everybody who has any advice.
Irina's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=868
View this thread: http://www.excelforum.com/showthread.php?threadid=26345
what type of text does your range contain. Could you post an example?
...place cursor in a specified text box in a Word form
Hello, I have a Word form developed in vba and when a specific textbox
is empty a message box appears to tell the user to enter some data
before they can proceed. When they click ok to continue the cursor
places itself in textbox4 on the form but I want it to be in the box
they have left empty (in this case textbox5).
I have used the SetFocus method but that doesn't seem to do anything,
it still ends up in textbox4. Any ideas anyone?
What sort of a form? If a protected form see
<>>< ><<> >...Will Windows' format mark bad sectors?
The external (USB connected) hard drive I use for backups apparently has
developed some bad spots. Formatting the drive starts producing System
Event Log entries at about 12% into the format, with the decription being
"The driver detected a controller error on \Device\Harddisk3\D."
If I allow the format to continue, will it eventually plow its way through
the bad area and mark those sectors as bad, or is this whole exercise a
waste of time?
Bert Hyman St. Paul, MN email@example.com
Waste of time would by my feeling!
When a "modern" drive has enough er...formula is displayed as literal text instead of formula result
I have been having trouble frequently with formulas appearing as litera
text in the cell instead of a formula with a result.
Does anyone have any suggestions as to what I may be doing wrong
carlossaltz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2420
View this thread: http://www.excelforum.com/showthread.php?threadid=38396
The cell you're working in is probably formatted as text. Try changing
the format to something appropriate.
If you are entering the formula cor...Formatting a List Box for Uppercase
I'm trying to format the data in a List Box to be upper case. I've tried
">" in the field properties, but that doesn't work. Below is the SQL
Statement in my query.
Any idea where I would insert something to make the First_Name and Last_Name
fields to be all upper case letters?
SELECT DISTINCT [tbl_PMS_Persons].[FIRST_NAME] & " " & [LAST_NAME] AS Expr1
ORDER BY [tbl_PMS_Persons].[FIRST_NAME] & " " & [LAST_NAME];
Thanks in advance,
Try changing the Row Source of the list box
SELECT DISTINCT UCas...Formatting Odd Number Strings
I have imported a text report into Excel. For some reason the date
string on the text file reads as 20/05/0728. Is there a quick way to
reformat the string so it reads 7/28/2005 or even 2005/07/28, for that
matter. I really don't want to manually edit 300+ lines! Thanks.
Assuming your fubared date is in column A, insert a new column
immediately to the right of that, and enter this formula:
This parses the existing text string into arguments used by the DATE
function, which generates a date useable by Excel.
What is the cell...date problem--excel changing entry
I want date to show as 11/05/03 and not have to enter
the / or a -. Have formatted cells to date and that but
excel ix changing to a totally different date----7/19/06?
You could use a macro that converts numeric entries to dates.
Chip Pearson has shared his:
Or if you're consistent (leading 0's for days and months), you could format the
column(s) as text, do your data entry and then use data|text to columns to
convert them to real dates (choose mdy as the field type).
D Blount wrote:
> I want date to show as 11/05/03 and...Need help formatting text
I am using OE 6 to read newsgroups and I sometimes post articles that I find
on the web, often copying and pasting articles into new messages. However,
when I do this, it formats it in such a way that when I open my posts, the
paragraphs are separated by at least 3 lines, rather than 1 line.
I have tried adding an additional step of pasting articles as unformatted
text in word before I paste them in a new message in OE. It seems that no
matter what I do, however, I get this weird extra spacing between the
Anyone have any idea how to solve this problem?