Time Calculations / If Statements
I am needing to do a calculation on time results, however,
when my time difference involves results from times of
2400 and greater, my result is inaccurate. See below:
2400 0015 (formula: f1-g1) answer is: 2385, but should
be 15. 2400 is midnight, 0015 is 12:15. I can't figure
out how to enter formula so I can calculate difference in
late arrivals when it's midnight. Would an "if"statement
fix this, if so, how do you do that? Or how do I fix this
problem? Your help would be GREATLY APPRECIATED! :)
Regard...IF statement #8
is there a way to say to create a column that displays a Yes or No if part of
a field has some text in it
for example if you wanted to tag the ones that had a D in column A
=LEN(A1)<>LEN(SUBSTITUTE(A1,"d","")) ......................... TRUE/FALSE
=IF(LEN(A1)<>LEN(SUBSTITUTE(A1,"d","")),"Y","N") .......... Y/N
Bernard V Liengme
Microsoft Excel MVP
remove caps from ...Error of "application-defined or object-defined error" in Goalseek
I always have above error while running below code in the excel 2007,
please help me out!
Source = Cells(8, 7)
x = Cells(7, 7).Value
Source = X*X +X*300
sorry adjust the code little bit by removing "End With"
Source = Cells(8, 7)
x = Cells(7, 7).Value
"Edward Wang" wrote:
> Hi all,
> I...Hourly Sales Totals including Taxes
Can anybody provide me with an hourly sales report that includes taxes?
There is an hourly sales report available in the reports section at
customersource but it doesn't include tax(es). Also if this report could be
chronologically sorted that would even be better (right now it shows 10:00,
11:00 etc before 7:00, 8:00 etc)
Dear Alex, let me know which report you are talking can you send me this
report on email@example.com also yes it is possible to customize the
report whatever way you want.
> Can anybody provide me with an hou...Writing Formulas to Include All Rows in Worksheet
What is the proper technique for writing a formula that includes all
rows in a worksheet? The number of rows in the worksheet change
frequently. So, for example, if I wanted to sum all of the rows from A1
through A1000 today, but A1 through A1100 tomorrow (because of new rows
added), I would have to update my formula to include the new rows. I
could write my formulas to include rows with no data in them (e.g.,
=SUM(A1:A1048576)), but is there a better way to write a formula that
automatically "expands" as new rows are added?
Thank you in advance,
-- ...SOP Statements
We are using GP v 9.0. I have just recently taken on the task of sending out
customer statements. I find that the User Definable Form has a great layout
that I would like to send to our customers. However, our company name and
address does not print on the form even though the option is marked when
selecting the statement. Any suggestions on how to get the company
information to print on the SOP Statement - User Definable Form?
If you're talking about the statements printed using
Tools>Routines>Sales>Statements, then you'll need to modify t...Define a name in VBA
I need a VBA code to select a non-active sheet(TEST) and then detect
non-blank area to define a name(GI) to it.
Thanks in advance
Dim LCol As Integer, RCol As Integer, TRow As Long, BRow As Long
LCol = Cells.Find("*").Column
RCol = Cells.Find("*", [IV1], , , xlByColumns, xlPrevious).Column
TRow = Cells.Find("*").Row
BRow = Cells.Find("*", [A65000], , , xlByRows, xlPrevious).Row
ActiveWorkbook.Names.Add "GI", _
RefersTo:=ActiveSheet.Range(Cells(TRow, LCol), Cells(BRow, RC...Printing Statements
User is mass printing customer statements and has to print
them individually. Has anyone experienced this problem
that can help with a solution.
...Synchronizing between 2 offices some thoughts please
I am looking for ideas on keeping a database synchronized between 2 offices,
London & Manchester. I know the easiest way would be to create a web based
database which we would both log into and process from that point, but I
assume we would loose the efficiency of the local reporting and other
aspects of a local copy of our data.
The other thought would be for us to pass data between the offices on a
frequency and maybe run an update query to sync the data.
The database is a contacts database so it is mainly updating call notes made
during the course of the day.
Any thou...How to prevent add ins being included in distribution worksheet?
The personal workbooks that I use on my machine use several home grown
add ins, some of which link to other workbooks on my machine.
However, the business workbooks that I produce and distribute to my
clients don't need those addins or the links.
How can I prevent my personal addins from becoming part of my business
workbooks so that my clients don't get warnings about links not being
Convert the formula with links to values is one way.
If I have a UDF (say) that is useful to others, I won't put it in my
personal.xl* workbook. That's for stuff that only I use.
...Self Referencing If Statements, Iterations
Previously, I created several self referencing If Statements that
would return data when a toggle cell was equal to what I was looking
for and would not change if the toggle was equal to something else.
This had the effect of providing a data table without having to have
the input functions on the same tab as the table. For example, on an
excel merger model, on a cases tab I had many different "cases" (one
company acquired another at a 10% premium, 15% premium, 20%, 25%, 30%,
etc.). On another tab in cell A15, I had this if statement =if(CASES!$A
$1=A$14,RESULT,A15) then I would have...Statement Date
I enter my transactions manually for some of my accounts, ie utility bills.
These transactions always have different payment dates, statement closing
dates, service dates, etc..
Is there any way to enter the statement date on money 2007 deluze version
220.127.116.113? I would like to do this so that I know what month I have just
In microsoft.public.money, OfficeNDN wrote:
>I enter my transactions manually for some of my accounts, ie utility bills.
>These transactions always have different payment dates, statement closing
>dates, service dates, etc..
>Is there...Nested Conditional Statements
I have a column within a report that has several versions of a particular
entry. So I wrote a series of queries that would help to identify the
different versions, see below:
Version 0: IIf(InStr(Report![Column],"Business")>0,"Version 0","")
Version 1: IIf(InStr(Report![Column],".1")>0,"Version 1","")
Version 2: IIf(InStr([Report]![Column],".2")>0,"Version 2","")
Version 3: IIf(InStr(Report![Column],".3")>0,"Version 3","")
Version 4: IIf(InStr(Re...I thought is was supposed to learn
Every transaction I make with my debt card always has the same store at the
payee "Moka House" and it is always lists as a "Insurnace" it is driving me
crazy. Can anyone suggest what I can do to fix this to make the program
In microsoft.public.money, Donnie Peterson wrote:
>Every transaction I make with my debt card always has the same store at the
>payee "Moka House" and it is always lists as a "Insurnace" it is driving me
>crazy. Can anyone suggest what I can do to fix this to make the program
Go to Accounts&...Crystal Report for Account Statements - accountReceivableHistory,
Rather than using the built-in account statements, I'd like to do it in
Crystal so that I can have full control. I'm hoping that someone else has
done this work and can perhaps provide a Crystal report.
Thanks for reading.
is there a switch statement that i can use in a cell as a formula?
i have a value in col a.
in col e i want that if the value in a is 1, it should return the valu
in col B, if it's 2, return col C if it's 3, return col D.
I know that i can do this with a nested if statement but i prefe
if someone can please tell me how to do this i would really appreciat
tkaplan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2298
View this thread: http://www.exce...Define view in a Task dashboard?
I've built a couple of Outlook digital dashboards in the past, but I'm
having trouble with my latest in Outlook 2003. Hopefully someone here
can help. Please forgive the cross-posting; I wasn't sure in which
group I'd find the most people familiar with digital dashboards.
In the past, the below code worked perfectly.
<OBJECT ID="Tasks" height=500 width=437
<PARAM NAME="Folder" VALUE="Tasks">
<PARAM NAME="View" VALUE="Simple List">
<PARAM NAME="Nam...Define name box size too small for viewing large formulas
When we create a new name using insert and define name then if the formula
is long it becomes difficult to view the whole formula. Also one is not able
to use navigation buttons left,right here as if we do that then the formula
gets modified. is there any way to see the whole formula. Like for creating
ever expanding validation list using Debra's technique one uses offset etc
but one is not able to view the whole big formula.
Please guide me.
try hitting F2 while in this text box. After this you should be able to
navigate within this box.
Frank ...OFX statement download now need to SAVE instead of OPEN
Bank of America offers the option to download statements
from their website. I used to be able to ask for the
download and get the typical Internet Explorer dialog
about whether to "OPEN" or "SAVE" the file. I would
click "OPEN," the .OFX file would process, and Money
would say "IMPORT COMPLETED."
For the past several weeks, this has been broken. I
can "OPEN" the file all I want, but Money does not
If I "SAVE" the file to my harddrive instead as a .OFX
file, and then double-click on that file, Mo...AR Statement in Crystal
I am currently working on producing the A/R statement in Crystal 9.0. We are
running GP 8.0. Crystal doesn't seem to be able to pull in all the
customers. I am using table RM00101 and pulling custnmbr and custname. The
inactive field is set to no and adrscode to 'STATE'. When I run this query
using smartlist from within GP, I get a count of 823 customers. When I run
this in Crystal, I only get 251 customers returned. Any ideas?
In Crystal, are you restricting on RM00101.dbo.INACTIVE with the value
"No"? If so, that should be 0 (0 = Active, 1 = Inactive).
Y...IF Statement #25
I am attempting to use an IF formula to do the following:
IF sales are less than $75 no commission
IF sales are >=$76 but < $150 1% commission
IF sales are>=$151 but <$300 2% commision
IF sales are> $300 3% Commission
The Commission paid is stepped at each point and any amount in betwee
the breaks also must have the commission paid.
Could some one please provide some pointers as to the right way t
Daniel Sloan's Profile: http://www.excelforum.com/member...APAYs thought I thought were cancelled are still being sent
I had cancelled APAYS in Money 2003. No problem. Now I
have upgraded to Money 2004 and these long dead APAYS are
automatically being transmitted to my bank and I cannot
prevent this from happening because they are not
appearing on my sceduled bills calendar. I have no
apparent way of preventing this so I have been resorting
to cancelling payment after the transaction appears in my
Call your bank and tell them that you tried to cancel but since it didn't
work, you need to have them manually cancel the Apay.
"Carlos" <firstname.lastname@example.org> wrote...Modal dialog not always receiving user defined messages from PostMessage()
I have a simple dialog based application that reads data from a serial
port in a worker thread (MonitorSerialPort()). When the worker thread
detects data, it uses PostMessage() to send a message to a custom
class that I derived from CWnd (CSerialDataProcessor).
CSerialDataProcessor takes the raw data from the serial port and
parses it into application specific format. It then posts a message to
a modal dialog and the modal dialog in response to this message
updates a control to display the parsed data to the user.
This scheme works for the most part, however, when I stress test...Statement Line Items not purging off statement
I have a few customer statements that although payments and returns have been
applied to invoices and paid in full are not purging off their statement??
There are a few different ways to achieve this outcome.
This method is when NOT using National Accounts.
I find most customers do not want to run the Paid Transaction Removal
process too soon, in case of voids and credits are required.
We modify the RM Statement and add a cacluated field to supress the body
when the current transaction amount is zero.
First step is to link the RM Open File to the RM_Statements_TRX_Temp, us...If statement- formula
Well I'm stumped again.
If close!ag2:ag19999 contains "xyz" enter contents of close!ag2:ag19999,
otherwise enter contents of close!ae2:ae19999
what do you mean with 'enter contents'?. Do you want to add them? If
> Well I'm stumped again.
> If close!ag2:ag19999 contains "xyz" enter contents of
> close!ag2:ag19999, otherwise enter cont...