Over 1 million tech questions and answers.

Solved: Calculating Date Fields in Excel

Q: Solved: Calculating Date Fields in Excel

Tech Support Guy System Info Utility version 1.0.0.1
OS Version: Microsoft Windows 7 Professional , Service Pack 1, 32 bit
Processor: Intel(R) Core(TM) i5 CPU M 520 @ 2.40GHz, x64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 2993 Mb
Graphics Card: Intel(R) HD Graphics, 1272 Mb
Hard Drives: C: Total - 234274 MB, Free - 193142 MB; E: Total - 2048 MB, Free - 2010 MB;
Motherboard: Hewlett-Packard, 1722, KBC Version 29.36, CND037022T
Antivirus: McAfee VirusScan Enterprise, Updated and Enabled

I would like to add a formula that would automatically calculate a date field from one cell and add three years. See example attached, Thanks in advanced

RELEVANCY SCORE 200
Preferred Solution: Solved: Calculating Date Fields in Excel

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Calculating Date Fields in Excel

Read other 7 answers
RELEVANCY SCORE 93.2

I need to use formulas to caluculate a given number of workdays before a given date.

For example:
A project is due on August 31

I need to figure
2 workdays before that date
7 workdays before that date
13 workdays before that date
26 workdays before that date

Can anyone help?

Thanks
 

A:Solved: Excel 2003, Calculating a date

Read other 7 answers
RELEVANCY SCORE 93.2

Now I need to calculate a date from a given date using 13 workdays prior if the given date is a Monday and 14 workdays prior if the given date is a Wed.

Is that possible?

Thanks
 

A:Solved: Excel 2003, Calculating a date using ifs

This will give you the dates for those and a blank for any other days:
Assuming your date is in column A
=IF(TEXT(A1,"dddd")="Wednesday", A1-14,IF(TEXT(A1,"dddd")="Monday",A1-13,""))
 

Read other 1 answers
RELEVANCY SCORE 80.8

Please help. I am trying to calculate a percentage of my yes's. I have several yes/no fields in which I'll need to calculate a rate. I would prefer to create a report that will calculate this for me. However, right now, I am really just looking for the easiest/quickest way to do this. So if I need to run a query first and base the report off the query, I'll do it. Please help ASAP if you can.

Thanks,
Tonya
 

A:Solved: Calculating Yes/No Fields in Access

Read other 16 answers
RELEVANCY SCORE 78

Hi people! I'm new to this forum, and have not been looking around much, but will from now on. While i'm a computer technician, Excel was never a strong point - i was always more of a 'hardware' guy. In any event, i have a question that needs some answering. Bear with me if some of my terminology is not %100 correct. I'll try my best.

I have a 3-page spreadsheet. On the 1st page, i have the values I input. Column A(A5 and down) is the date column. B - S are the numerical monetary values I put in, and T & U are the totals. One is a numerical total, the other a monetary total. On the 2nd page, Column A is again dates(B is reserved for manual input), and C & D are the row totals from the 1st page. I manually input data from E - M, and Column N is =SUM(B2:M2). You get the picture.

Here's where it gets tough. The 3rd page is just a break down of the monetray charges for any given day. What i want is for the 3rd page to update the date on the top to the day it is now, and then the monetray totals for that day only, or the day before, whichever I need. I guess What i want is if I change the date on the top of the spreadsheet page, it will update the totals in each of the cells i have underneath. It's not going to be printed, just for viewing on my screen. Right now, I have to input the data for a daily printout manually. If i can change the date at random, and get it to update the totals from the other page, that would be great.

That bei... Read more

A:Can Excel Automatically Update My Date Fields?

Read other 16 answers
RELEVANCY SCORE 75.2

I'm trying to subtract one date from another date.
Example:
10/27/1942....mm/dd/yyyy
10/05/2010....mm/dd/yyyy
answer............yyyy/mm/dd

Formula needed will subtract 10/27/1942 from 10/05/2010 and the answer to read as
years/months/days.

Could you please help with a formula if this can be done the way I would like?
Thank you for your help
 

A:Solved: Date Calculating

Read other 11 answers
RELEVANCY SCORE 70

Hello, I'm trying to figure out a way to calculate time fields in a database, I would like to know how to add time fields from two text boxes so that the amount of hours will display in a Total Hours text box. I am using two text boxes, Start Time and End Time, Total Hours is where the total will be displayed in amount of hours from the two time fields, an example would be 8:00 AM (in Start Time) and 10:30 AM (in End Time) and 2.5 being in Total Hours. On a form, when typed in the Control Source property for Total Time, =DateDiff("h",[Start Time],[End Time]) seems to work, but it is not inputting the sum in the main table where all of the fields are kept, is there any way the total can be done and kept in the main table? Also, I would like to include a Total Hours text box with the totals of the Total Time fields because I would like to add more text boxes on a form. Can somebody help?
 

A:Access 2000 Calculating Time fields

That's the only place you can do it is in a report or query. I don't think you can calculate directly in the same table for fields within that table. Try creating another table and calculate the date fields within that table. Is there a reason this time difference needs to be stored in a table?
 

Read other 2 answers
RELEVANCY SCORE 70

In an OpenOffice (version 3) text document it is possible to insert a table containing merge fields that are populated from a relational database.

It is also possible to perform calculations on cells, as in a spreadsheet. That is, a formula editor allows a cell to contain a formula that will display the result of a calculation on two other cells of the table.

This works perfectly with hard-coded data in the cells but I have been unable to find a way to perform calculations on cells containing merge fields representing data from an external source. All attempts to do so have produced an "Expression is faulty" message with no further hint. I have been unable to find anything relating to this in the somewhat chaotic OpenOffice documentation.

The aim is to convert a spreadsheet-based invoicing system to run from a relational database, thus eliminating duplicated data such as company names and addresses, and making integration with other documents using some of the same data easier and more efficient.

Can anyone help, please?
 

A:Calculating on fields extracted from database in OpenOffice

it may be the imported data from the data base is TEXT rather than a number to calc
in excel you can use =value() to convert a number thats formated as text into a number

I just tested in OpenOffice Calc and it has the same issue

format a cell as TEXT put a number in and it is right justified and will not calc
use =value() to convert to a number
now you can calc
 

Read other 3 answers
RELEVANCY SCORE 69.2

Hello.

I am trying to create a contract template for our lawn care business. There are 22 different services that our customers can choose to add, each with a price specific to their own property. At the bottom of the service and pricing list there is a "sub-total" line, with a "5% discount" line underneath, and under that the "total" line.

Like so:

weekly lawn maintenance $35.00 Is there a way to "make" these numbers add up, and
Shrub trimming $150.00 appear in the areas below? So that we don't have to
Fertilization $35.00 manually do all of the calculations, thereby saving
ourselves literally days of work.​Sub-total $220.00
5% discount $11.00
Total $209.00

Also, on the contract we have payment options for our customers. Another big time consumer has been manually calculating these options.

Like so:

$209.00 payable at a flat rate over 9 months (March through November) = $23.22/month
If I can get the "total" number from above to automatically calculate and "jump" to the figures in red above, that would be simply wonderful.

I know that in excel the calculations can be done by creating a spreadsheet (although I do not know how to make a spreadsheet), but I this contract is a legal document and needs to appear so. I'm hoping that Word has a way to do this as well.

FYI, I tried to upload the document but for some reason the only way I can get to the template is if I want to create a docum... Read more

A:Help creating automatically calculating fields in word 2007

Read other 6 answers
RELEVANCY SCORE 69.2

Hello all...I've been having calculation issues in excel 97. Let me start by letting you know that the "Automatic Calculation" feature is engaged. Here's my problem:

I have a spreadsheet that contains four tables of formulas: 1)Deficiencies by Material Weakness 2)Deficiencies by Business Process 3) Total Deficiencies and 4)Aging of Deficiencies. The problem with this is that the sum of the Def. by MW and Def. by BP do not equal and neither of the totals concur with the Total Deficiencies.

Other issue happening, I've entered a formula to calculate the aging of outstanding items. When I enter the formulas, it calculates and displays the totals as I desire. I save the workbook, go into another workbook and do some work, but when I come back to the first workbook, the calculations are all "0" and aren't calculating.

Please help....this is bugging the crap out of me because I've seen the formulas work.
 

A:Solved: EXCEL 97 Not Calculating

Read other 16 answers
RELEVANCY SCORE 68.4

Excel gives different answers for same formula.
Formula =SUM(F4*I1*E1)
Actual values: 0.300, 31, 113
Answers given: 1055.27 and 1054.19
Actual answer (via calculator) 1050.90
Parms checked:
Rounding
Auto Calculations = on
Cell formatting
Accounting w/2 decimal places for dollar amounts in calculation cells
Number with 3 decimal places for F4
Number with 0 decimal places for I1 and E1

Illumination is most appreciated.
 

A:Solved: Excel not calculating correctly

Read other 8 answers
RELEVANCY SCORE 68.4

I have developed an Excel spreadsheet that is to be used as part of an Invoice/Delivery Note system. I have used a dropdown box to allow the user to select the level of discount applicable on a particular sale and then this should be deducted however this only works on single item sales not when a product is being order in quantities greater than 1. This is because the formulas are programmed to use the item price and work out the discount on that, I can't figure out how to get it to calculate a discount on the total cost.

Does anybody know how I can achieve this because this is the only flaw left in this system?
 

A:Solved: Calculating Discounts in Excel

Your formula in J20 to J27 in the invoice worksheet should be

SUM(Qty*Price)-(Qty*Price*Discount) for the second part of your IF statement

not

SUM(Qty*Price)-(Price*Discount)
 

Read other 3 answers
RELEVANCY SCORE 67.6

Hi,

I was using excel on a spreadsheet and it was working fine, until I got up to spin my monitor around to shows something to my boss, and caught the keyboard while excel was open on another spreadsheet.

When I've come to do some standard average calculations which has worked in all the previously imputted cells, the calculation now just equates a 0 (zero), for every calculation I do, even simple 1+1=2, is now 0?!?!?!

Help I have a deadline to finish this speadsheet for this evening...

Cheers
Amanda
 

A:Solved: Excel 2003 - Not calculating correclty

Hi,

I've worked it out, I corrupted Excel itself probably from a bad command which I had inadvertently inputted. I have exited totally out of all excel files and re-opened it and the formulas are now working fine.

Excel moaned a little bit, but after closing and opening it again I had no problem.

Cheers
Amanda
 

Read other 1 answers
RELEVANCY SCORE 67.6

I have a spread sheet that holds all the data for the services we support.
We need to report on availability/downtime of our service.

Each service has different supported hours i.e Mon-Fri 0800-1800 , some may include bank holidays some include saturdays.

I need the spreadsheet to add the 100% possible availablity of the service (in minutes) for that month - i.e October = 31 days , 9 week end days no bank holidays (UK) = 60 Minutes x 24 hours (= minutes in 1 day) * (31-9). so 100% availability = 31680 minutes...

So basically i need to populate columns 'G' and 'I'

I have added a calendar to the spreadsheet that calcualte where the holidays are...

To be honest I think I am confusing myself here and I need someone elses view perspective - all ideas welcome !

Please help!

Kind Regards

Phil.
 

A:Solved: Excel - Calculating Availability in minutes

Read other 8 answers
RELEVANCY SCORE 67.6

Hey all-

I run weekly reports for my company to track our agents call volume. I then add up the weekly reports for one big monthly report. The problem that I have is when I add the weeks together, the summation for Total duration time of calls is way off. The format that I am using is hh:mm:ss. If I click on the cell it shows as AM or PM. I think that what is happening is that when the hours add more than 24 it starts back at 0 again. Is there way to set it up so it would show total calls like this, 65:23:12 (hh:mm:ss). To represent 65 total hours spent on the phone?

I appreciate it!!
 

A:Solved: Calculating time in Excel 2003

Format the cell that you're using as the sum. Under format cell you can go to custom and type in the formula [h]:mm:ss
 

Read other 3 answers
RELEVANCY SCORE 67.6

I have a field that reads 2009-12-30-15.42.22.81298 as text. i want to make in be a date of 12/30/2009 15:42:23 and ignore the milliseconds. Is this possible?

Thanks
Ted
 

A:Converting complex date text fields into date or numbers

Ted, what software are you talking about?
Are there always 8 values in the date part?
 

Read other 3 answers
RELEVANCY SCORE 66.8

I am trying to make a form for my medical plans that will allow me to calculate different rates based on different decrements. Now in english - lets say I am currently paying $10 for a doctor copay and the corresponding rate is $450. Now if I were to change the copay to $15 the rate would decrease by $2.50 or if I change it to $20 it would decrease by $5.00. I would have a drop down with the various copays ($10,$15,$20,etc), next to it would be a cell that reflects the $450 rate.

I want to make a form so that when I change the copay it will automatically subtract the corresponding decrement for that copay. For ex when I change the copay to $15 I want it to automatically subtract the $2.50 from the $450 thereby showing $447.50 as the new rate or if I change it to $20 it will automatically subtract $5 showing $445 as the new rate.

The other problem is I have several of these copays/decrements that have to be calculated such as specialists and prescription drugs.

So basically how would I go about creating a form with several dropdown boxes with various copay options that will calculate the decrements for those rates and give me a final rate.

I already know how to make drop downs I just don't know how to get it to both correlate the copays with the decs and then calculate the final rate.
 

A:Solved: Calculating formulas using drop downs in excel

Read other 16 answers
RELEVANCY SCORE 66.8

I was able to calculate average time in my original spreadsheet in Excel 2003. However, when I copied data from that spreadsheet to another & attempted to calculate average time it doesn't work! It keeps returning the error code #DIV/0! which I assume is because many of the times I have (they are all in military time) have a zero in the beginning (i.e., 0:01:25). I would just change the time to regular time, but it needs to stay in the military format. Also, the column that the cells are in are formatted as time. What can I do to get past this?
 

A:Solved: Calculating Average Time in Excel 2003

Read other 10 answers
RELEVANCY SCORE 66.4

Hi there,
I have an MS Access database (2000, but primarily operating on PC with Access 2010). The database logs wagons coming to and from our site. Mostly, these wagons are in and out in the same day, but it is possible for them to park up overnight, for which we charge a small fee.

The situation has now changed slightly, in that we now accept wagons staying on site for longer periods (usually a weekend), so I've been asked to introduce an Exit Date field as well, and correct the existing calcualtion of time on spent site.

Currently, the following fields appear in the main table (called Stays):

Arrival Date
Arrival Time
Exit Time

There is also a field called Duration, which (for some reason) is autofilled by an Update Query, with the Update To value set to:

IIF([Arrival Time]>[Exit Time],1440+DateDiff("n",[Arrival Time],[Exit Time]),DateDiff("n",[Arrival Time],[Exit Time]))​

This is clearly inaccurate - it assumes that if a wagon's exit time is earlier in the day than its arrival time, then it must have stayed for 1440 minutes + the (negative) difference between the two times, and if the exit time is later or equal to the arrival time, the wagons has been in and out of site in the same day.

As wagons could be on site for multiple days (so the 1440 mins figure could be wrong) and could leave x amount of days plus x amount of minutes after arriving (so the assumption that it's exited the same day as it arrived could be wrong),... Read more

A:Solved: MS Access modifying a date and time calc in separate fields

Read other 8 answers
RELEVANCY SCORE 66.4

EXCEL SOS!!! I have an urgent deadline which I'm likely to fail if I can't get my head around this issue so please please feel free to comment / offer suggestions...I'll try anything!

I have around 10 - 15 sheets within the same excle file which is effectively acting as one tab per resource within my consultancy team. These are acting a a record of the billable & non billable hours per resource and therefore each line is primarily driven by date (2011 - 2013), with 1:N ratio items of the following: Client, Contract, Role, Days & hrs (effectively 1 line per date, resource, client & contract combination + time billed within the std 8 hr working day).

I have no problems creating individual pivot tables in the individual resource name's relevant tab, however the next step is to roll this information togather to show a holistic view for all resources, all clients etc (in what I anticipated to be a pivot table via the consolidation of multiple ranges function) for all resources by exactly the same fields:

Year, Date, Contract/Type, Resource, & Date - at row level (with possible additional options of Month, Week, Day being added as a row label)

AND

CLIENT , Non Billable - at column level

Naturally there may well be some slicing and dicing of the info needed to understand the figures at different levels, ie per resource (days billed & rate), how many hours used per contract, how we've used time in a contract (by role), & whom (by... Read more

A:Solved: Pivot multiple sheets for text, date and decimal fields

Hi there, welcome to the board!

I feel your frustration. Unfortunately your data structure isn't conducive for a PivotTable data model. But there is hope! You have some options here. First of all we need to know what version you're using. If you have 2010, well, you're going to really like what I'm about to tell you, and if you don't have it, it's a good reason to upgrade! 2010 has a new add-in called PowerPivot. Think of it as PivotTable's on bulk steroids. The benefit you would see - the ability to select multiple data sources. This means you can have multiple data tables/sheets as your data source, and select their relationships. Think of it like an Access table/relationship. It's the best feature we've seen in Excel since the PivotTable itself.

So if you have 2010, this is good news. However, if you don't have 2010, we need to consolidate your data to a single data structure. Whether that is putting it all on one sheet, or exporting to Access, or whatever, that's what needs to happen.

I'm not entirely sure what you mean about not getting the row level structure. Is there any way you can post the file?
 

Read other 2 answers
RELEVANCY SCORE 65.6

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

A:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

Read other 16 answers
RELEVANCY SCORE 65.2

Hey all,

I am looking for a general reference list of the most common used math operators that can be used in Excel 2003 and the format that would go in the corresponding cells when they would be applied. There are the obvious (Sum +) Subtract (-) Multiply (*) and (Divide) (/).

If there are way too may to list, to keep things simple for users, can anyone provide a list the top 20? I'm looking for a reference list that goes above basic math, say up to Algebra II. But I don't need or want Calculus formulas either.

The point of this list is to show beginning Excel users how this powerful spreadsheet program can do more than add, subtract, multiply, and divide numbers in a cell. Please include these basic functions in the list as well if you can. Thanks in advance!

Jack
 

A:Solved: Need List of General Math Calculating Operations For Excel 2003?

Read other 6 answers
RELEVANCY SCORE 63.6

I have created a macro that filters information and have created a submacro that updates information after the macro runs. Here is the submacro:

Sub macro1()
For MY_ROWS = Range("L65536").End(xlUp).Row To 1 Step -1
If Range("L" & MY_ROWS).Value = "CANCEL" Or IsDate(Range("L" & MY_ROWS)) Then
Rows(MY_ROWS).Hidden = True
End If
If Range("L" & MY_ROWS).Value = "N/A" Or IsDate(Range("L" & MY_ROWS)) Then
Rows(MY_ROWS).Hidden = True
End If
Next MY_ROWS
End Sub

What I need now is to add a column in front of A1 and calculate a new date, which would be the date in column L minus two days. Is this possible or would I need to have column A blank 1st rather than inserting a new column? By the way, column A already has data.

Thanks for your help.
 

Read other answers
RELEVANCY SCORE 63.2

Hello, not sure how to explain this but hopefully this will work.

I need to combine the contents of all the cell Bís when cell A has the same contents (in this example 2222 etc.).
........A......B
line 1 2222 object
line 2 2222 box
line 3 2222 something
line 4 2223 circle
line 5 2223 square
line 6 2224 stuff
line 7 2225 glass

When Iím done, my spreadsheet will look like this.
........A......B
line 1 2222 Object, box, Something
line 2 2222
line 3 2222
line 4 2223 Circle, square
line 5 2223
line 6 2224 stuff
line 7 2225 glass

or this. It doesn't matter if the the original contents of the B cells stay as they originally were.
........A......B
line 1 2222 Object, box, Something
line 2 2222 box
line 3 2222 Somthting
line 4 2223 Circle, square
line 5 2223 square
line 6 2224 stuff
line 7 2225 glass

I've been manually cut and pasting but there must be a better way. Microsoft Office Excel 2007

Thanks in advance!
 

A:Solved: Excel help combining fields

Read other 9 answers
RELEVANCY SCORE 63.2

Hi guys,

In a previous thread I have asked how to conditional format fields within a certain time frame and have been given the answer =TODAY()-[No of days].

This works sucessfully, so that if I want to mark every date more than a year old in red the command is =TODAY()-365.

Is there a way that I can then count these fields?

Basically.. after they have been marked red I want to know how many reds i have in each column..

Sorry... im an excel newbie .

Thanks
 

A:Solved: Excel, Counting certain fields

Use the CountIf function.

Select the range of cells that you want to have counted and then enter a condition like >365

That should do what you want to do.
 

Read other 1 answers
RELEVANCY SCORE 63.2

Hello,

I would like to delete these fields from Excel. Clicking on them I have error. How can I delete them ?

Regards,
 

A:Solved: How to delete these fields in Excel ?

These are not fields, these are either buttons or objects to which a macro is attached.
Normally you should be able to click on this and choose (right mouse) to delete.
Or the sheet is protectd or the item is protected
 

Read other 3 answers
RELEVANCY SCORE 62

I'm having troubles with getting a total sum from multiple subreports. I'm getting #name? and I believe
this is because Access is confused as to what to total, could be from using the same names for fields?
I have checked and I don't see that I have used the same names, but I'm not an expert with Access.

Attaching a sample database with by reports. Report1, the field I'm totaling on is CompAmatcost, in Report2, the field is CompAnonmaterial. Report3 is the main report where the totals are being calculated. The data criteria for both reports comes from the costing sheet form and the queries that
manipulate the data are encloed in the test database.

Any help would be appreciated.

Thanks!
 

A:Access subreports problem in calculating fields from multiple subreports

Read other 16 answers
RELEVANCY SCORE 60

G'day everyone - I suspect this issue will be a tough one to get help with!

I've attached a screen-capture of a Microsoft Office Excel 2007 spreadsheet I'm working on at the moment. As you will be able to see on the screen-capture, I've marked the areas of interest and will explain here...

When I enter a name in this column (marked as '1'), I want the spreadsheet to check another column (marked as '2') to see if there is a match. If there is a match, I then want the spreadsheet to check the column marked as '3', and subtract that amount from the column marked as '4'. If there is no match between the column marked as '1' and the column marked as '2', I don't want anything to happen.

I hope that made sense, and if anyone can help me with a solution here, I would greatly appreciate it.
 

A:Solved: Calculating Side-By-Side Cells Dependant On Text Entries In Excel

Read other 8 answers
RELEVANCY SCORE 59.2

I have set up an Excel spreadsheet that contains peoples dates of birth (eg cell E2) with the cells formated as date entry.
I have also entered a formula to calculate each person's age in years and months on today's date using the DATEIF and NOW commands. It seems to work perfectly:

=DATEDIF(E2,NOW(),"y") & "y " & DATEDIF(E2,NOW(),"ym") & "m "

However, when I sort the individuals AGE, Excel ranks 11 before 3.
How can I alter my formula and/or cells to create the months as 03 to compensate for this?
 

A:Calculating age on Excel

You are going to find it difficult to truly sort your data using your current method. Since you are concatenating the letter "Y" and "M" to your year and month calculations, you are forcing the cell to have a text format instead of a true number. Its not a good idea to sort numbers that are stored as text but if you insist, the only way to deal with this is to add "IF" logic to evaluate the length of the number returned by each of the DATEDIF functions and add a zero to it if it is less than 2.

The formula would be similar to the following

=IF(LEN(DATEDIF(E2,NOW(),"y"))<2,"0"&DATEDIF(E2,NOW(),"y")&" y ",DATEDIF(E2,NOW(),"y")& " y ") & IF(LEN(DATEDIF(E2,NOW(),"ym"))<2,"0"&DATEDIF(E2,NOW(),"ym")&" y ",DATEDIF(E2,NOW(),"ym")& " y ")

Also, by placing both the years and months in the same cell you will only be able to sort on the first number (year) which is being treated as text. If you store the year in one column and the month in the other you could sort by both criteria.

Your best bet is to use two seperate columns formatted as true numbers to hold each of the numbers. The first column (year) would have its own DATEDIF formula
DATEDIF(E2,NOW(),"y") and would have a custom number format of ## "Y"

The second column would have its own DATEDIF formula DATEDIF(E2,NOW(),"... Read more

Read other 1 answers
RELEVANCY SCORE 59.2

I was working in a worksheet and all of a sudden it stopped calculating; I went to to Tools/Options/Calculation and the automatic calc box is ticked but I still can not calculate, copy formulas etc. I am desperate and on a deadline...any help?
 

A:Excel not calculating

Try unchecking the Automatic Calculation and making it Manual, then hitting F9. Then go back and reset the option to Automatic and save.
 

Read other 2 answers
RELEVANCY SCORE 58.4

Hello. I am using Excel 97 here at the office. I have numerous spreadsheets that have formulas in them. About a week ago, the spreadsheets quit automatically calculating the formulas. Say the formula is to calculate the sum of a column of numbers. I change on of the numbers in the column and the formula does not automatically update until I save the file.

Also, I copy a formula a lot and paste it to cells along a row in the spreadsheet. Now I get a message that says "cannot empty the clipboard". It still lets me complete the paste function but only after hitting the OK button on the error message twice. Rather annoying since I do this all day long.

I've searched Excel's stupid help feature everywhere and cannot find an answer. I, also, searched Microsoft's tech support section and no help there either. Please help!!!!!!
 

A:Excel not calculating formulas?

Check to see if the calculation mode is set to automatic by going to Tools/Options/Calculation tab and tick automatic.
 

Read other 2 answers
RELEVANCY SCORE 58.4

Hi,
I've been looking around for a solution to this for a bit but I don't think this is something I'll be able to figure out on my own. I'm not really as much of a competent Excel user as I'd like, so if anyone can help out, I promise to learn from it!

Basically, I've been sent a spreadsheet containing opening hours for a list of locations over the period of a month (split by date). The opening hours are entered in a single cell like 10:00:00 - 14:00:00. I was asked if there was a way that we could calcuate the number of AM and PM openings in seperate columns at the end. So the 10am - 2pm would be 1xAM opening and 1xPM opening.

I hope I've described this okay. If this can't be done, I'll just tell them to do it manually.

Thanks!
 

A:Excel - calculating AM & PM shifts

Read other 7 answers
RELEVANCY SCORE 58.4

Hi everyone ... first post here.

I have a fairly complicated Excel workbook which was getting difficult to maintain so I changed a lot of cell-based calculations to VBA functions. The VBA functions are easy to read, can be commented and use global variables for defining cell, row, column and worksheet locations. Big improvement.

But - it is now so slow it's painful. I can watch the status bar grinding through the progress towards "Calculating cells: 100%". When I run in debug mode with breakpoints, I notice that some of the functions seem to be executing twice. For example I have one function that calculates values for 4 cells and it executes 8 times; I cannot see why this might happen for the life of me. Sometimes the arguments to the function are empty when they clearly should not be. So, I have a few direct questions:

1. Any ideas why functions might calculate twice?
2. Are there any ways of monitoring the way a workbook calculates cells so that I can follow the 'program flow'? (Just trying to step through using Shift+F8 would take far too long)
3. Any suggestions for tools that might help me to analyse the way in which the calculation speed could be improved?

For reasons of compatibility I have to be working with Office '97. I have tried switching calculation mode to manual in Tools|Options, but the client just complains all the time that nothing works. And doesn't seem to be able to learn to use F9.

OS: Windows XP SP3 + updates

T... Read more

A:Calculating VBA functions twice in Excel

Read other 16 answers
RELEVANCY SCORE 58.4

I have attendance which says P for present, A for absent and N for non working day. So, I contact number of absent, present or non working day using CountIF.
Now, my question is to if a person is Absent (A) before and after the non working day (N) like A N A, then the total number of Absent becomes 3 A instead of 2 Absent (A) and 1 Non working day (N).
How to do it? Using a macro or any function.
 

A:Excel help for calculating attendance

Read other 10 answers
RELEVANCY SCORE 58.4

I have certain cells set to hh:mm and when I total them up it gives at as a time rather than total number of hours. For example if it adds up to 20 hours 30 mins it shows as 20:30 but if it is 24 hours 30 mins it shows as 00:30. How do I get it to give the answer as 24 hours 30 mins or 24:30?

P.S this is in excel
 

A:Calculating hours in excel

Hmmm.... well I have a solution, but it might not fit your needs.

Say I have 2 cells with time information in them, A1 and B1. If I place the following function in the total cell, it will return text with the totals you need formatted as you wanted. However, they will not be time formatted and so may prove difficult to work with later. Here is the function:

=CONCATENATE(HOUR(A1)+HOUR(B1),":",MINUTE(A1)+MINUTE(B1))
 

Read other 1 answers
RELEVANCY SCORE 58.4

I would like to know how I can calculate hours worked in an excel worksheet. If I input time in and time out can I get a whole number sum? Example Time in:8:00 Time out 4:00 How can I get my answer to say 8.0?
 

A:Calculating Hours In Excel

Read other 10 answers
RELEVANCY SCORE 58.4

I am trying to come up with a simple spreadsheet that will allow me to enter a list of song times in a minutes:seconds format in column A and it will give me a cumulative total of all entered in B1. I tried a couple of things, but it was becoming a multi-leveled transition. I know someone has figured this one out before, so I figured why reinvent the wheel. Can anyone help?
 

A:calculating time in Excel

Read other 16 answers
RELEVANCY SCORE 58.4

i am trying to calculate a time sheet in excel which subtracts end-time from start-time and also subtracts a break, this works fine but when I try to use autosum to total the hours its wrong. I have tried re-formatting the number style etc and created custom formats but to no avail, is there a function/format that i'm missing

please help
 

A:calculating time in excel

Read other 7 answers
RELEVANCY SCORE 58.4

Hi - I would like to create an Excel worksheet which can calculate monthly commission for my sales team (11 agents) in a fair way reflecting the calls they have taken, hours worked and individual sales while also incorporating the performance of the department . Currently individual performance is not used and the commission is 1 pence per item for all sales above 70% of the monthly target and 2 pence per item above 100% of the monthly target. For example, if we sold 55,000 items with a month target of 50,000 each agent would receive £250 commission (15,000 x 1 pence & 5000 x 2 pence). With this example an agent could have sold 12,000 items, worked 100 hours and taken 7 calls per hour while another agent could have sold 6,000 items, worked 80 hours and taken 4 calls per hour.

Does anyone have any ideas as to a way to calculate commission in a fair way using these criteria?? Any help would be appreciated. Thanks
 

A:Calculating commission in Excel

Read other 6 answers
RELEVANCY SCORE 58.4

Hi,

I was wondering if someone could tell me how to calculate a formula in excel as, for example:

=A1+(B1*B2) And what I need is the brackets to be to the power of -1 or +1. Basically this is to calculate interest or inflation (depending if the power of 1 is + or -).

Thanks a lot,
Dan
 

A:Calculating 'to the power of...' in Excel

hi dmurgitt,

The character you want is the caret, like a little roof, on my keyboard it is shift + 6 (not numeric keys) along the top row.

So 5 to the power of 3 would be 5^3.

Anything to the power of 1 is the same, so why do you want that?

Cheers
Hew
 

Read other 3 answers
RELEVANCY SCORE 58

Hello, I use online accounting software (KashFlow) and part of entering invoices involves clicking into a date input field.  It is one of those where a little calendar shows when you click into the field.  You can also manually type a date into this field too. However. On my brand new Yoga 920 using Chrome and Firefox the date input fields are greyed out and a red 'no access' icon shows when you hover over them.  But you can still click it and the calendar pops up as normal.  The only difference is you can NOT type into them anymore. When the source for the page is viewed it looks like the fields have been designated 'readonly'. There is no such problem on any other computer I own, even when using Chrome or Firefox.  The date fields work perfectly well on these other computers - even on a Sony Xperia tablet I own. The only browser that works properly on my Yoga is Edge.  The date fields work as they should.  It's just Chrome (my main browser) and Firefox where they don't work. This seems like a Lenovo-based problem.  A little Google has revealed a couple of similar problems with other Lenovo users. Does anyone have any clue on how to fix this?  This is utterly bizarre behaviour and it's preventing me from working properly. I have attached a photo showing the problem when using Chrome.  These fields should never be greyed out. Thanks,Matt







... Read more

Read other answers
RELEVANCY SCORE 58

Hello,
I need some help with a formula which will allow me to calculate dates in reverse. I have critical dates which are not adjustable (project letting dates) and need to calculate dtae for plans review.
I need to calculate 1 day prior to the let date then
28 days prior to the one day prior date,
then 60 days prior to the above date.
then 90 days prior to the 60 days date.

the formula needs to be day, month and year sensitive.
I have attempted and continue to recieve a circular error message.
To date i can not see where I reference the same cell but i guess I had to have done this.

Any suggestions would be appreciated!
 

A:Calculating Dates backward in Excel

Read other 9 answers
RELEVANCY SCORE 57.6

I have been trying to help a friend learn how to do form letters with his Microsoft Works Suite 2004 programs. He has Windows XP SP2. We were able to successfully merge desired fields from a Works database but noticed that the format of date and time field results was not as desired. The date field always merged as "MM/DD/YYYY" in the form letter no matter what format was used for date display in the database program. He wanted to display, for example, "November 12, 2004" instead of "11/12/2004". Likewise, the time field merged as "hh:mm:ss AM/PM" when he wanted, for example, "11:25 PM" to display.
In Works 4.5 (on another computer), his formatting carried over to the form letter from the database. Not so in the 2004 version. Our temporary fix, was to format the date & time fields in the database as "text" instead of "date" or "time" and then enter the exact display style wanted. Then, the merged value looked the same in the letter as it did in the database.
Is there a better solution to this problem?
 

Read other answers
RELEVANCY SCORE 57.2

I am working on a project that is requires analyzing the hours a machine is offline, AND excluding the hours the factory is closed, which is between the hours of 1 am and 4 am.

Ex:
Machine went down 1/2/12 9:03 PM
and was back up at 1/5/12 1:43 PM

What where the total hours machine was down, excluding the hours it would have been down on a daily basis anyways (1 AM TO 4 AM).

Does anyone know a formula that would make my life so so much easier?
 

A:Calculating Total Operating Hours in Excel

Read other 16 answers
RELEVANCY SCORE 57.2

Hi all

I am hoping someone can help me with a formula for timesheets. Our company pays everyone the following rule for weekends:

? On Saturday, Time-and-a-half for first 2 hours, Double-time anything after that
On Sunday - double time all day.

My timesheet template calculates time and a half and double time during the week using the IF formula, however how can I calculate the weekend rate?
I have attached a timesheet template.
Regards
meganl
 

Read other answers
RELEVANCY SCORE 57.2

I am struggling with Excel's time/date format and could use some help.

I have a pre-populated spreadsheet with one cell containing an elapsed time value in the form HHHH:MM:SS and appearing in the cell like 1122:12:46.
When viewed in the formula window it appears as: 2/15/1900 6:12:46 PM

I have another cell that contains a mileage value (ex: 20,777). I want to divide 1122 hours by the miles (20,777) value to get average speed (18.5), but I cannot figure out how to extract the 1122. I've tried a number of different ways but the closest I came was getting 6 for the hour, which is not what I want. I believe that Excel is treating the number in the cell as a value that it converts to date and time, rather than taking it as the actual time value that it is intended to be. Since Excel thinks it's something different it won't let me use a simple RIGHT() to extract what I want, nor can I get it to convert to a string (Text () gives me 46.76).

Help please!

Thanks in advance.

John
 

A:Help calculating speed using elapsed time in Excel

Read other 8 answers