Subtract 12 hours from date/time
I am trying to subtract 12 hours from a date/time cell if the date in B1 is
greater than A1.
A1 3/3/2010 2030 (formatted: m/d/yyyy hh:mm)
B1 3/4/2010 1030 (formatted: m/d/yyyy hh:mm)
No matter how I format A1, B1 and C1 I still get a #Value error. I am
obviously missing something or leaving something out. Any help is greatly
Valid date & times would like: 3/3/2010 20:30 else your date and times are
"Cindy" <Cindy@discussions.microsoft.com> wrote in message
I have a problem with some calculations in a spreadsheet.
I have some numbers that have functions tied to them to figure out the value
I want to show...
I now have other cells H20 = B30
H19 = H15
H23 = h20 - h19.
All cells are showing a currency format. But when you do the subtraction
the H23 shows something different (Looks like a rounding issue).... It is
simple I want to calculate what is shown in the cell. What do I need to do
to do this?
...Keeping my cash expenses from subtracting the cash balance
I have Ecell 2003 and I have just started a Family budget. I have inputed
everything and it is working fine,Till I input what I pay for in cash and it
also subtracts from my cash balance.
I do not want that, I want a clear picture of where I am spending my money,
Not to alter something to make it add up to my checkbook. I woould apperciate
any help. Thank you
I'm certainly confused. When *I* spend cash, it makes *my* cash balance
lower. Have you found a way to spend money that doesn't? If so, please
MVP Microsoft [Publisher]
How to ask a qu...Subtracting dates to get hours... but I want to skip weekends
In column A I have start times (DD/MM/YY HH/MM/SS) and in column B I
have the stop times. I am measuring the hours from the difference of
these columns for each row, in column C.
However, I would like to exclude weekends from the subtraction. I.e
Friday 5 pm is the start time, Monday 8 am is the stop time, then the
difference would be 15 hours instead of 63 hours.
I know how to convert the fractions returned from the difference, into
hours/minutes/days, and I also know how to convert dates to "day of
week", so unless you guys know of a good idea I was just going to sort
the file...I need to add and subtract time cells. ie. 13:45 minus 13:58
I am having trouble manipulating cells formated to time. I need to be able
to add, sum, subtract the entered cells, and then work with the results of
the addition etc.
To add times and prevent them being converted into days, Cusstom Format as
"Mschertz" <Mschertz@discussions.microsoft.com> wrote in message
>I am having trouble manipulating cells formated to time. I need to be able
> to add, sum, subtract the entered cells, and then work with the results of
> the addition etc.
...Money adds when should subtract
Money did work, but suddenly when I enter a withdrawal,
Money adds it. I've checked the transaction to ensure
they were marked as an withdrawal.
Where is this occuring and are you using the transaction
form or doing it inline. For some reason or another when
I try to do it inline, it always messes me up...withdrawls
are added and deposits subtracted. I don't think its
money per se just how my mind perceives the fill in blanks.
So I just use the transaction forms. No need for any
negative or subtraction keys.
>Money did work, but suddenly whe...Subtracting Time in Excel
My employer does payroll manually. They look at timecard timestamps and then calculate how many hours I worked. Then they forward that data to the company that prints the paychecks. My employer is often wrong on calculations. I am trying to do a worksheet where I enter my start/stop times and get the correct results to check against employer's calcs. Doing an AM to PM shift is no problem, calculations are correct. However when I try to get hours worked from a Start PM to Stop AM ( let's say 11:09 PM - 7:53 AM) shift all I can get is #######. Does anyone have a tip on how to do ...subtract half an hour from hours and minutes
could somebody please point me in the right direction here. Im tryin
subtract 30 minutes form a cell containing a number of hours an
minutes formatted using [h}:mm custom cell forma
MajorGeneralDes's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=370
View this thread: http://www.excelforum.com/showthread.php?threadid=31945
Any of these will work:
>could somebod...Date Field Subtraction
I have problem with Excel (2010) subtracting dates. The date1 is
entered into COLUMN L and a second date entered into COLUMN U when
information sent. The end formula in COLUMN W (below) works fine until
there is no date posted in COLUMN U (i.e. blank) then always sees as -
figure and shows "LATE".
Please help - would like status to show NULL in COLUMN W if nothing in
COLUMN U entered.
Formula in Column V:
Fomula in Column W:
TIME",IF((V334<0),"EARLY",IF((V334>0),"LATE",I...subtracting columns in a macro
I'm trying to subtract columns in a macro, but since the
resulting file will be exported in *.csv format, I only
want to subtract the rows that have data in them. Can
someone tell me how to do that?
Thanks so much!
...how to subtract one row from the other?
how to subtract one row from the other?
Use a simple formula like
Copy this formula to the right as far as you need to go.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"prasad" <firstname.lastname@example.org> wrote in message
> how to subtract one row from the other?
...subtract 4000 hrs from 10000 hrs
i have a problem at work where i have to subtract one set of hours and
minutes from another, i.e. 13234:34 - 1234:58. The problem that I have is
that excel does not recognise above 9999:99. Does anybody have an idea as to
how I can do this, be it a conversion, macro or a simple aside formula?
Format the cell as [h]:mm
good as long as the result is zero or positive.
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"M...how I can sum or subtract 2 or 3 field in access in any record
I want know that why there is NOT any relationship between access and excel
and how I can sum and subtract 2 or 3 filds in access in any record
Hello Hadi -
I'm not sure I understand exactly what you are asking, but Excel is *not* a
'front end' for an Access database. You can use Excel to query the db & copy
Access data into a workbook in several ways. That copy of the data can be
used for calculations, but the data *doesn't* get changed in the Access
file. Although the modified results can be imported to Access it may very
well be t...2 Que's regarding dates (matching and subtracting)
I'm trying to do the following:
In C8, users enter in a date.
In K30, I want to display the number of days until the end of the month,
using the C8 field as the start date/month.
Example: User enters 10/8/2004 in C8
K30 should equal 23.
It also needs to accomodate the calendar year 2005, and so on. Is this
The above answer may also take care of this, but what is the date format if
I want to use a subtraction or matching formula.
Example: A1 = 2/1/2004
I have a column of months (J), and a corresponding set of values (K). I...How do I use a "subtract" function in Excel?
if you want to subtract the value in B1 from the value in A1 and have the
answer display in C1 then in C1 type
Hope this helps
"Kelly" <Kelly@discussions.microsoft.com> wrote in message
...Comparing numbers and subtracting from a total
Here's what I'm wanting to do and it's driving me nuts trying to get
I have a worksheet for calculating my Weight watchers points and
progress and such. I have almost everything working except this one
I want to have it compare my total daily points (25) to my total
allowed points (28). If my total daily points are higher than my total
allow points, I would like it to calculate how many I'm over and then
subtract those from my weekly flex points (35).
I believe this is possible but I'm not sure if I'm using the correct
Let me know if I di...Subtraction formula with Text error
This is a timesheet:
A1 B1 C1 D1 E1 F1
Time 9:00 am 12:00 pm 9:00 am 12:00 pm OFF
Total 3 3 #Value!
I have time in and time out with total hours caculating. The formula I am
using for total hours is a simple C1-B1 with HH:MM format.
I need to enter OFF, SICK, OUT for days that the employee is not in but I
get an error value. How can I make it so that the formula adds the hours
entered and ignores any other values such as OFF, SICK, OUT.
>The formula I am us...Column sum incorrect upon subtracting
I have a column with numbers that is totaled at the bottom. The Auto Sum
formula correctly adds all the cells. I tried to make two of the cells
negative so that they would be subtracted. But, the cell amount is being
doubled before it is subtracted from the total. So, if the cell amount is 50,
for some reason 100 is being subtracted. I have all cells formatted as
numbers, not general or text, and have checked for hidden formulas (there are
none). Here is my example to show what is happening:
B2 = 5
B3 = 5
B4 = 10
B5 = 15
B6 = 15
My total is correct at 50...Subtracting 2 dates
Birthday = 5/1//1946
I am using a formula to get the age of a person =(TODAY()-Birthday). This
returns a number 22272. I know that I can format that number and return the
correct year - 60. But I want to calculate off of that number. I am doing
an IF this year = certain age, I will get a certain text script.
But my formula does not see the =(TODAY()-Birthday) as a 60. How do I show
an age in years that can be calculated from?
(there's no email, no snail mail, but somewhere should be gmail in my addy)
I have two worksheets in the same document with a long list of Email
addresses. I want to use a Query to create a new list that has only
the Email addresses that are not present in both lists (i.e.
In Excel 2003 I tried going to Data, Import External Data, New
Database Query and it made me select another worksheet. When I
selected the one I was working on it said there are no tables in the
worksheet. (Whats the difference between a column of data and a
Anyway, am I going about this the proper way to perform a Query?
Thanks for any assistance you can offer,
Bria...How to change the Subtotal of my Pivot table to use subtraction instead of Summing
I have the following Pivot table created:
Sum of HEAD_COUNT DEPT
PER_GROUP TYPE HQ HRD ED PD CFD
--------- ---- -- --- -- -- ---
ACCOUNTING 1APPROVED 0 0 0 0 0 0
2ACTUAL 0 0 0 0 0 0
ACCOUNTING Total 0 0 0 0 0 0
ADMINISTRATIVE 1APPROVED 0 1 0 0 0 0
2ACTUAL 0 0 0 0 0 0
ADMINISTRATIVE Total 0 1 0 0 0 0
CORPORATE SUP 1APPROVED 1 0 37 1 8 7
2ACTUAL 1 0 35 1 8 7
CORPORATE SUP Total 2 0 72 2 16 14
----------------------...Adding and subtracting hours in Excel 2000 worksheet
I'm having some success with the addition part but not with the subtracting part!
I have an Excel spreadsheet that is used to keep track of time worked by staff on a weekly basis and I need to keep track of the overtime worked. For example, one staff member has worked 40 hours, 54 minutes and 25 seconds in a week (displayed in a cell as 40:54:25 with the cell formatted as Category: --> Time and Type:--> 37:30:55).
I then went to subtract 40 hours from the total hours worked to get the overtime displayed and thats where I'm having the problem. I tried the format of "=F9 -...How do I subtract today's date from one before 1900?
I'm attempting to create an Excel timeline of battles and major eveins in
United States history. I want one of the column's to show how many years it
has been since that date.
For example, say A6 has July 4, 1776. Cell A1 has the formula =Today()
Every time I use the formula =Year(A1)-Year(A6) I get an error message in
Sadly, dates only go back to 1900.
"Bob Martin" wrote:
> I'm attempting to create an Excel timeline of battles and major eveins in
> United States history. I want one of the column's to show how man...Date subtraction -How to not show negative when 2nd date not entered
Hi, I have a section of a worksheet that just needs to show working days
between two dates.
For example, an order was entered on 9/2/07 and complete on 9/15/07
The formula =NETWORKDAYS(A1, A9) works perfectly except that until the
"completed date" ise filled in, a negative number appears.
Is there a way to have that remain blank until both dates have been entered?
change your formula as
If you think my answer is useful, please rate this post as an ANSWER...subtracting units of time
I am trying to keep track of my hours at work, as I need to make up a day I
spend at hospital during the course a month. I have put together a sheet
(sort of a cut-down version of my department's timesheet), with day, month,
in and out. What I want to do is subtract 30 minutes from each standard
work day, so I end up with 8 (or however many hours worked) minus my half
hour lunch break.
Any help would be greatly appreciated.
Auckland, new Zealand
Stick 0:30 in a cell and name it lunch_break.
Then use = A1-lunch_break, where A1 contains your times be...