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

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.)

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,""))

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

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

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

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

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!!

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

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?

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

Hi everyone,

I'm very much a beginner and struggling with the following table regarding mid-year report dates on the attached worksheet.

I am trying to get cells in column R to go: AMBER when it is within 1 month of the due date and RED when it is overdue unless there is a Y in column S, in which case it would go GREEN.

I have attempted conditional formatting and am getting nowhere.

I should be grateful if an Excel genius out there could point me in the right direction.

I have attempted conditional formatting and am getting nowhere.Click to expand...

should do it

this will test for 31 days less

in one of the conditional format rules put in 3 rules

=if(S2="Y", true, false) make green

=IF(AND(TODAY()>C2, TODAY()-31<C2), TRUE,FALSE) make amber - within 1 month

=IF((TODAY()-31>C2), TRUE,FALSE) make red

see attached spreadsheet - tests Column C2

Hi everyone,

I'm very much a beginner and struggling with the following table regarding mid-year report dates .

PQRS1FROMTOMid-Year DueM-Y Complete201-Dec-1130-Nov-1231-May-12Y301-Nov-1131-Oct-1201-May-12Y401-Jul-1130-Jun-1330-Jun-12Y530-Jul-1230-Jun-1313-Jan-13N601-Jun-1231-May-1330-Nov-12N701-Feb-1231-Jan-1301-Aug-12Y

I am trying to get cells in column R to go: AMBER when it is within 1 month of the due date and RED when it is overdue unless there is a Y in column S, in which case it would go GREEN.

I have attempted conditional formatting and am getting nowhere.

I should be grateful if an Excel genius out there could point me in the right direction.

I am using Excel 2003 windows XP Pro. I am having difficulty filerting the dates. I want to be able to sort different data using the mm/d/yyyy format. Is there a way to do this? Thanks

This is my first time posting so please bear with me...

I have a spreadsheet with two columns containing date and time stamps and I need to calculate the hours elapsed between the two date/times excluding weekdays and holidays. I tried using the NETWORKDAYS formula but it only considered the dates and reported the results in days, but what I need is hours. Not business hours, just hours. So for example:

Column F Row 7 shows 5/18/2009 21:01

Column G Row 7 shows 5/20/2009 22:27

I need a formula that will consider date and time in each column and report the time elapsed in hours.

Any help or advice on composing my formula would be greatly appreciated!

I have used this to calc from one day to another -

eg - 22.30 --> 01.00 next day

where I2 = 22.30 & I3= 01.00

=I3+(I2>I3)-I2 (this gives a total of 02.30 hrs)

its a start!!

Basicly all I want to do is get a count of entries in a list, if the date in the cell is less than today. The formula I have been trying to use is =COUNTIF(O73,(O73<TODAY())) but its not working so I am obviously doing something wrong can anybody help ?

I have a workround by using a hidden column and a few other arguments etc. but its cumbersome I like the elegance of a simple formula and it should be easy !

The syntax for countif is =Countif(range,test)

Range should be a range of cells, your formula will at most return 1 if O73 < today.

If you want to count all the ones between O1 and O100 you'd put O1:O100 in the place of range.

Test works on the cell being inspected automatically so you don't need to repeat the cell. What you do need is quote marks (not parentheses) around the criteria. For example to count the numbers in O1 to O100 whose value is less than 4 you'd write =countif(O1:O100,"<4")

Because Today() is itself a function it doesn't go within the quotes. It is joined to the less than sign by an ampersand. So in your particular case you'd want

=COUNTIF(O1:O100,"<"&TODAY())

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

Hi, I'm a beginner when it comes to formula for selecting date ranges! I have tried =SUMIF combos but and hitting a wall. Any help would be very welcome. Pretty basic the request.

2 colums, 'A' with the year (currently as plain number) 'B' with a simple number. There are roughtly 5/8 entries for each year, from 1989 to 2008. I need to sum 'b' for a range of years, (say 1990 to 1995), I can do it for one year value but not more.

Just tired this =SUM(IF(D,">1995",E:E)(D,"<1990",E:E)) but did not work.

[I do have a slightly more complicated request of using real dates and being able on select all for the last 15 years for example and 15 to 20 years, but I think we should leave that for another day}

Any help very welcome, thanks.

In cell A2 I want to display the date that the worksheet was last modified.

Is there a function that does this?

Or is this a job for VBA? If so , how do I do this (from scratch)?

TIA, TTFN

I am trying to extract a date from a data cell within an Excel 2003 workbook.

The cell contains the data, "October 10, 2011 11:11:11 PM GMT-04:00" but I want only the date information and for it to convert to MM/DD/YYYY format. Basically, taking that cell and turning it into 10/10/2011.

I have about 500 entries with similar data and I am looking for the easiest way to extract the dates from the text. Mind you, the length of the Months (i.e., January vs. September), Date (i.e., 1 vs 11), and even time vary (1:00 vs 11:11).

Any help would be greatly appreciated!

Found the answer I was looking for:

Assuming the first cell is A1,

=TEXT(LEFT(A1,FIND(",",A1)+5), "mm/dd/yyy")

Copy down, then copy the column and paste values.

You can also use

=DATEVALUE(LEFT(A1,FIND(",", A1)+5))

then format the cell as mm/dd/yyy, then copy down.

Hi again,

This one should be much easier than my last issue ...

I'm writing excel macros and everything is going great except that when i tell my macro to save a particular file i need it to add that day's date (the current date) to the filename...

Any takers?

LTT

Oh yeah, Merry Christmas and Happy Holidays to everyone at Tech Support Guys!!

Hi All (please refer to entry #4 with updated excel)

Thanks for helping.

Thanks to Turbodante, I am able to sum a range of colunm data in a macro (refer below), but now I need to be able to choose how much of the column to sum using a date that it stops at.

So rather than doing the whole table, I need it to just sum, as per the example attached, from July09 to the top of the table. Also each month additional data will be added, so its needs to be able to keep summing the data from the date to the top of the table as new data is entered each month.

Also different sheets will need to pick up the date where it needs to end. This will be in the worksheet as a manual entry (refer cell E18)

Sub Macro1()

Dim bRow As Long

bRow = Range("b4").End(xlDown).Row

Cells.Find(What:="Cell Name").Offset(1, 0) = "=sum(b4:b" & bRow & ")"

End Sub

Thanks for your help

Strybes

10 more replies

Hi,I need to calculate the difference in working days / hours between 2 date / time stamps.Some simple example data which spans a weekend.01/07/2016 18:0004/07/2016 09:30(Working hours are 08:30-17:00 Monday - Friday)In this example the first date / time stamp is when an email was recieved (out of hours) and the second is when the email was replied to (1 hour into workiing hours) So the formula should return 00:01:00:00 (dd:hh:mm:ss)Thank you for your help!!

It's a rather lengthy formula and needs explanation, so see here:http://www.cpearson.com/excel/DateT...If you have further questions, please ask.MIKEhttp://www.skeptic.com/

14 more repliesHello 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.

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.

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?

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)

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.

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.

I have a date at the top of my spread sheet that automatically updates daily. I also manually input dates into cells, they do not change once I have entered them in. How can I make the inputed dates turn red once they have gone past 90 days of the automatically updating date? This is for Microsoft Excel 2003, thanks.

Use conditional formatting and create a new blank rule based on a formula. I have the =Today() in cell A1 for current date. In A4 I put an arbitrary day.

=$A$4<=$A$1-90 and set the formatting to red and bold.

I have an existing Excel spreadsheet that contains the date format mm/dd/yyyy. I want to convert it to mm/dd/yy. Excel will not convert the current cells to the new format without re-entering the information. Can anyone help? Thanks in advance.

I'm trying to format the date entry in a cell (in MS Excel 2003) (e.g. from 20-Jan-2012 to 1/20/2012)using the "Format" feature, but it doesn't work. I wonder what's the problem.

Hi, welcome to the forum

Have you tried the Custom format?

Choose under Format Cell the Date format then click Custom and put is as you require, should work

I've attached a sample with three formats to explain it

Hi,

I wish to change the current date in a footer to the file (WSheet) creation date.

Normally my files simply carry this date, but due to an outage, my wsheets show the current date. Or to put it another way, the present date as shown is the current date, not as I wish, the creation date.

Platform: WinXp Office Excel 2003 (11.8307.8221) SP3

David

I go to format cells number date then pick the style I want from the list in my case 03/14/98 format the cell them go back in and enter 031498 and it changes it to 3/27/86 help me out

This should be posted in the Office Forum here:http://www.computing.net/forum/offi...Home Page http://ewen.uuuq.com/

5 more repliesHi, Please excuse me if you see that I posted this message in the general Software yesterday. I am definately new to this board.

I wish to change the current date in a footer to the file (WSheet) creation date.

Normally my files simply carry this date, but due to an outage, my wsheets show the current date. Or to put it another way, the present date as shown is the current date, not as I wish, the creation date.

Platform: WinXp Office Excel 2003 (11.8307.8221) SP3

David

-I am having trouble with date formats.

-I can format cell, and type of format date shown, but the date will convert say from 040808 to 08/18/2010, oh that auto date format Excel is doing only changes the date by about 4 months,10 days & 2 years off what entered, after Excel converts to date format.

-it happens no matter what date style I chose in the cell format; ie. Mar 31, 2008; or 02 mar 08, or any of the options date format offers.

-any idea of why? it's not the tools>options>calc>1904 issue..already tried that solution...could the base date (1904 data corruption maybe) that the excel program uses have been altered?

-or any other suggestions.

I am trying to import an excel file into access. Everytime I import it two of my date columns get imported as the date serial number instead of the date value. All of my other date columns import just fine, and are formatted the same within the excel file. The two columns that import in the wrong format have "Unknown" in place of the date. What can I do to get the dates to import as a date value and the "Unknown" cells to import as "Unknown" into access?

As you’ve figured out, it is these ‘unknown’ entries which is causing Access to change the rest of the column to their serial numbers. Access is very particular in the data formats of each column, so the fact that dates are mixed with text does not sit right with it.

I would suggest that you leave those unknowns as blank.

Also, I believe OBP will be along shortly to correct what I’ve said.

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!

Hi All,

Is there a way to use the .Find method (or other) to find each date within a range (eg "1/1/2012" to "2/2/2012")?

Can you explain in real life terms what exactly you are trying to accomplish. A sample workbook would also be helpful.

Rollin

Hi,

Just noticed if I insert Jun-15 in a cell i get 01/06/2015 in the formula bar (also noticed that entering june15 etc, defaults to jun-15.

But entering 15-Jun gives 15/06/2006 in the formula bar (all ok)

My Regional settings are set to UK

This normal & built into Excel?

I am trying to find a formula for counting up to a calendar date from a calendar date. That is, column A, row 1 is the umber of days to count up. Column B row 1 is the date I want to count from, Column C row 1 is the result. It would look like this when finished

33 1/3/2008 2/5/2008

Is such a formula possible?

Michael

Well this does it, but I am not sure if you should use a date diff function

=B1+A1

Hello!

I'm hoping someone here can help me with this Excel issue.

I have a few Excel 2007 workbooks with thousands of rows of data over 80 columns. About fifteen of these columns are date formatted. In 2007 the format is perfect. I convert it to 2003, save and close the 2003 version. Open the 2003 version back up and ALL the date columns have switched to general. (shows only 42293, etc).

Changing the fields to date doesn't seem to do anything as the moment I close and reopen the excel file it is converted back to General. Its like Excel 2003 is storing these values as Number instead of Date.

My first short-term solution was to copy the entire spreadsheet and Paste-Special into a new workbook but I am supposed to use these files month after month. My second solution was to manually enter an apostrophe before each date but that caused problems as the field was then read as a text field instead of a date field. Plus it was majorly time consuming.

I don't seem to find a reliable solution to this issue on the Microsoft website and I've only seen one other example that gave the above solutions as their replies. I'm stuck...

Thank you for your help!

Renea_dell

Hi there,

Strange indeed. Is this in one of the files, or all of them? Do you have any macros in the file? I can't recreate your error. Can you recreate this in another file? And if so, can you post it?

Hi Guys

I am new to this forum and not very technical minded so please bear with me.

I have a monitoring spreadsheet through which i monitor c40 word docs. The biggest issue i face is that these words docs have a box with an expiry date on it. I normally have to open each word doc every week and update the date to current date. Then i go back in spreadsheet and manually update a column which has conditional formatting and goes red to alert me. For my purpose i will have the same date in all word files

WHat i want to achieve is to have a button in my excel file which when pressed updates the date in all word docs to current date. Word files will be closed during the update

I hope i am making sense

Thanking yo uin advance

Regards

Hi All!

I am having major difficulty figuring out excel. I am using a spreadsheet and want excel to automatically send an email to the user in that row when a contract is expiring. Within the row I have the specified user's email, the end date of the contract, and when the reminder email should go out. I have tried playing around with Macros and VBA coding, but I have no idea what I am doing. I am using excel 2003. Any help would be greatly appreciated!! I am using Outlook as my email. Have questions please let me know!

-J

16 more replies

Hello

I have a report showing movement dates for items. I need to show the newest movement and give the details.

So this is column A ( item ID), B ( movement date) then the other details are from c onwards.

1054 16/07/2008

1054 11/07/2008

1057 22/10/2008

1057 09/06/2008

So how can I show the newest movements only ( the sheet has lots of different items on it)

Hope this makes sense!

Hi

=B1 > DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))

-- assumption "16/07/2008" is at cell B1 and others respectively

-- below will return "TRUE" if the date in B1 is greater than within 1 month back from Todays date.

-- Once you do this for all the records you will have out put in one column as TRUE or FALSE

-- This might not have been a complete end to end solution, however to start with you have now at least the dates which are changed within a month.

Hope this helps

Rgds

Hi, I was kindly helped my AJ on this site to end up with

=SUMPRODUCT(((A7:A18)>=A6)*((A7:A18)<=B6)*(B7:B18))

for the folowing:

2 colums, 'A' with the year (currently as plain number) 'B' with a simple number. There are roughtly 5/8 entries for each year, from 1989 to 2008. I need to sum 'b' for a range of years, (say 1990 to 1995), I can do it for one year value but not more.

but the further problem I have is:

I have a list of 160 rows with the year being the key data field and selecting various ranges say 1995 to 2006.

I need to capture the data in a way that I need to keep historic info for years gone (2006,2007) but then alter the sheet (remove a row if no longer relevant) for current and future years.

I was thinking of having a final column with a "year removed field" and the main formula above being dependant on that for inclusion, make sense?

Many thanks

I have tried using the below formula to workout the difference between 2 dates and times.=NETWORKDAYS(J50,N50,'[Bank Holidays.xlsx]Sheet1'!$B$2:$B$9)-1&" Days "&HOUR(MOD(N50-J50,1))&" Hour "&MINUTE(MOD(N50-J50,1))&" Minutes"However I have noticed that this doesn't work when the end time is earlier than the start time i.e.Date 1 22/01/2015 14:53Date 223/01/2015 10:58Outcome1 Days 20 Hour 5 MinutesWhat it should be0 Days 20 Hour 5 minutesAny ideas?

Unfortunately, NETWORKDAYS() knows nothing about Time, it works only with Days.It's the Time part of the formula that is causing you the grief.Here is a different approach to the problem, see if it helps:http://chandoo.org/wp/2010/09/10/wo...MIKEhttp://www.skeptic.com/

8 more repliesI 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.

Help, I have two dates one in column F3 01/05/2015 12:07 and one in G3 06/05/2015 09:52 - both Uk format dd/mm/yyyy and time hrs and mins. I want to calculate the time taken between these two,our working hours 08:30 - 18:00 and business working days Mon -Fri. I want the answer to show HRS/MINS and everything I have tried has failed. I need the formula to deduct weekends if the range covers a weekend out of the answer, and to deduct the non working hours between 6pm and 8.30am. We run flexible holidays so no requirement to have a holidays column. Help!

Look at this thread and see if it offers any help:http://www.computing.net/answers/of...See my last reply # 11It's a bit complicated, so any questions please ask.MIKEhttp://www.skeptic.com/

11 more repliesI have been using the below formula for a while to calculate the time it takes to close our help desk tickets, in hardcoded business hours (5AM to 6PM) given the normal business days (Saturday and Sunday). It works like a champ - however, now I have a few people working in the Middle East and their weekend is Thursday and Friday - How can I tell this formula to count the business days Saturday thru Weednesday? Or any other forumal out there that will do that job? =IF(COUNT(H10,I10)=2,24*((NETWORKDAYS(H10,I10)-1)*("18:00"-"5:00")+IF(NETWORKDAYS(I10,I10),MEDIAN(MOD(I10,1),"18:00","5:00"),"18:00")-MEDIAN(NETWORKDAYS(H10,H10)*MOD(H10,1),"18:00","5:00")),0)

It can be done, it's a bit involved, here's a video:http://www.teachexcel.com/excel-tut...MIKEhttp://www.skeptic.com/

2 more repliesG'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.

Running Windows Vista Premium Home Edition.

I have an Excel 2003 spreadsheet.that I originally created on Excel Office Professional Edition 2003. Also, have Excel 2007 Home and Student Edition installed. I uninstalled the Excel 2003 software, but now when I clik on this created Excel 2003 spreadsheet with Excel 2003 it will not open. It says there is an invalid path. I reinstalled Excel 2003 and it opens. I tried to save this as an .xlsx extension and then uninstalled Excel 2003. It wouldn't open. So something is occurring here. On other Excel 2003 created spreadsheets, they open without issue.

Then I have a program with an .exe extension. It needs to open using Excel 2007. With both Excel 2003 and Excel 2007, it will not open. It freezes and then I get an error message saying it cannot open with another Excel other than 2007. If I right clik on the .exe program, there is no "Open with". I even tried opening a blank Excel 2007 spreadsheet and then cliking on "Open". but it is a bunch of gibberish. My IT person says you can't open the program that way, You have to launch it as an .exe program using Excel 2007.

Any ideas on what is going on? By the way, I have both Excel versions on my laptop running Windows XP and I do not have these issues at all.

Hi there,

If I said I was confused that'd be an understatement I think. You cannot open executable files (*.exe) with Excel. Recognized file types are things like .xls, .xla, (for 2007) .xlsx, .xlsb, .xlsm, etc. I don't know what your exe file is and I'm not sure I wanna know, but it certainly doesn't open in Excel. What is it exactly? If you don't know I wouldn't open it. Or if someone told you to open it with Excel then it's probably a virus. Unless I'm missing something here.

With the file in question which won't open, assuming it's not this strange and unknown exe file you're talking about, sounds like the file may have been corrupted. Does it open on any other machine? And if you uninstall Excel 2003, did you re-install 2007? It seems strange. I would try cleaning out your application and reinstalling 2007 fresh. You can't install 2003, then 2007, then take off 2003 and put it back on, you're gonna get things screwed up. I bet your registry doesn't know which way to turn here. Some good info on it can be found here...

http://officearticles.com/misc/best_install_procedures_for_microsoft_office.htm

Also, I would highly recommend you completely uninstall all versions, and I'm talking a deep cleaning, then reinstall. For that deep cleaning, take a look at this article (which I'd highly recommend you do) and then reinstall...

http://officearticles.com/misc/remove_microsoft_office_from_your_pc... Read more

Im using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':

Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()

Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant

Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range

Dim ServRange As Range, SrcOpen As Boolean, SourceName As String

Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"

'Exit Sub

'Sheets("LookUp").[a12] = Now

With Application

.ScreenUpdating = False

.StatusBar = "Counting source files ..."

End With

Set FilSrch = Application.FileSearch

With FilSrch

.NewSearch

.LookIn = ActiveWorkbook.Path

.FileType = msoFileTypeExcelWorkbooks

If .Execute > 0 Then

ReDim MyFilArray(.FoundFiles.Count)

For I = 1 ... Read more

We recently migrated from Office 2003 to Office 2010.

Just to give some background, (and I dont' know if this is relevant to this error), the Office 2003 excel did not accurately show the correct file paths where files were linked. It would show something in the C drive.

The issue I am concerned about today is this: when some users close files in 2010 (files were created in they sometimes get the error below:

'"The name ABE2, either conflicts with a valid range reference or is invalid for Excel. This name has been replaced with _ABE2"

Can someone assist me with this? I need to stop it from appearing and I am not sure how.

Hi everyone i have a database and in the database i have a invoice form. How do i get it to minus 20% for the cis deduction and then add 17.5% for vat? I am using access 2003 on windows vista.

Thanks in advance.

Karl.

the calculation should be

(value * 0.8 ) that should take the 20% off - for cis deduction

then * 1.175 should add the VAT

(value * 0.8)*1.175

My Excel spreadsheet has stopped calculating.eg Cell C45 = hours workedCell C46 = Hourly rateCell C47 = total (=c45*c46)for some reason no calculated figure is showing in C47, neither are the running totals at the bottom of each week or month.Has to be something simple, it was OK yesterday, but can I see the problem? No!any suggestions please.Ta, StrollerStroller

Have you turned auto calculation off?Tools>options>calculationCD

3 more repliesI 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?

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

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?

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

Hi folks,

Im creating a spreadsheet in Excel 2010 for claiming expenses (such as travel) at work. One requirement is that it should be possible to enter a price - either including or excluding VAT - and have the other amounts be automatically calculated.

Heres a screenie for the relevant part of the spreadsheet to help.

http://img820.imageshack.us/img820/5300/vatcalc.jpg

For simplicity sake we'll call the top left cell A1. So what should happen is that a user can enter a VAT excluded price, let's say ?60, in to the left column and the amount including VAT (?72) will appear in the right column with the VAT (?12) appearing in the middle column. And they should be able to do this in reverse, so enter ?72 in the right column and get ?60 and ?12 in the other columns. The totals of these 3 columns add up at the bottom.

Now I can place calculations for this in the respective columns (=C2/1.2 =C2-A2 =A2*1.2) and this works except when a user deletes an entry it will delete the formula with it, as well as that there's a problem with the two VAT exc / inc formulas going round in circles as they refer to each other.

Really cant think of a way around this. Thanks in advance!

I would have two parts. One for entry or either w/ vat or w/o Vat. Then have the calculation section to the right.

8 more repliesI have just been upgraded to Excel 2007. I find that some of the macros that were recorded in Excel 2003 no longer work in Excel 2007. We use the Analysis Toolpak VBA add-in and I have added this in to 2007 ok, but when running a macro I get a run time 1004 error ATPVBAEN.XLA cannot be found. After some searching it would appear that some of these functions have been renamed in 2007 e.g. it is now a .XLAM file rather than .XLA which is why the macro code can't find it. I guess I could manually edit all the macros and change the fucntion names but there are other colleagues in my team that are still using 2003, so I don't want to break things for them. Is there any solution to this? Thanks

Firstly I am a beginner of beginners but I try to learn as much as I can.

I have put the following protection code into an Excel 2003 workbook and everything runs ok. I then opened the workbook in Excel 2007, and saved it as a .xlsm file. When I try to open the .xlsm file in Excel 2007, I get a vba Runtime error 13, "Type mismatch", and the code sticks at the first line below("If Worksheets...). I have tried everything that my limited knowledge allows, searched the web for 2 days, and still have no answers.

Any help would be greatly appreciated.

Private Sub Workbook_Open()

If Worksheets(28).[A51] > "" And Worksheets(28).[A50] = Worksheets(28).[A51] = False Then

MsgBox "Sorry, this program is not registered for this computer, please e-mail .......... for more info."

Application.DisplayAlerts = False

Application.Quit

End If

End Sub

My system specs are shown below.

Tech Support Guy System Info Utility version 1.0.0.2

OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit

Processor: Intel(R) Core(TM) i3 CPU M 380 @ 2.53GHz, Intel64 Family 6 Model 37 Stepping 5

Processor Count: 4

RAM: 2934 Mb

Graphics Card: Intel(R) HD Graphics, 1243 Mb

Hard Drives: C: Total - 290143 MB, Free - 172186 MB;

Motherboard: Dell Inc., 0WXY9J

Antivirus: avast! Antivirus, Updated and Enabled

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

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

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

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?

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!!!!!!

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

Simple stuff I know but the brains not in gear as it should be today. I have an excel worksheet and want to work out salaries after tax e.g 22%e.g.in column A1 = £16,000 (salary)in column A2 = A1/52 to give weekly wagein column A3 = A2 * by 4 to give monthly totalin column A4 = stuck on monthly total less 22% to give salary after tax.... help much appreciated and hope this makes sense.Durko

First, A2*4 wont give accurate monthly total. It should be A1/12. In A4, =sum(A1/12)*0.78

10 more repliesi 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

I am creating a spreadsheet, and some columns will have cells with numbers in them expressed as 120/80 or 135/90 or 120/40 etc etc etc.I want these to appear exactly as I type them, I want Excel to accept them as though they are 'text'. Excel insists on treating them as a math and 'calculates' them and puts the answer in the cell.Can I stop it?DRiM

I am trying to get results for 4:00pm to 12:30am and i am just getting ############### I know that means the calcluation are incorrect but my same formula is working perfectly for 7:00am to 3:30pm. I also want this to subtract 30 minutes for lunch for anything over 6 hours. Can you help me?

I am trying to get results for 4:00pm to 12:30am and i am just getting ###############This formula will get a negative result, cause Excel assumes both given times are at the same day.message edited by paulsep

3 more repliesI 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

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))

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?

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

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.

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!

Created a worksheet with formulas, in Excel 97, and saved it on floppy and hard drive. Can open worksheet from floppy and hard drive in Excel 97 but not Excel 2003. Formula cells become #NAME?

Sample of formula:

='C:\blp\api\dde\Blp.xla'!BLP(C190&" equity","ask",,,RTD("BLOOMBERG.RTD","","IZU+UV Equity","ASK"))

How can I resolve? Thanks.

When I open a spreadsheet in Excel 2003 that was created in Excel 97, the pages with macros open with only the columns showing. It looks as if the row that appears has an infinate length. I've tried changing the row hieght, and everything else I can think of, but can't view the spreadsheet normally. Other tabs in the spreadsheet do not appear either.

Hi

I have a Users DB that I cannot make any changes on it. One of the fields has a Date field. I need to run a query on that table to create reports, but the query creates multiple records for the same user with the only difference in the Date filed. I need to have only the records with the latest date in the Date field.

Is there a way to set a query that does this?

Thanks,

Barbos

You need 2 queries, the first has the userrecordId and date fields, set Totals>Grouping the userrecordid Grouped and the date set to Max.

Then in the second query you want the main table and the 1st query joined by the userrecordid and the date.

So the first query finds the last and the second show the data from just that record.

Is it possible to design a query so that it prompts the user to enter 2 dates (eg: a start date and end date) in order to retreive data entered between those dates for a report?

This will retrieve payments made in between two dates, in order to prepare a deposit. It will never be a set time period, which is why I need to prompt the user for this information.

I may have to look into flagging payments as deposited, if thats not too complicated (say the secretary did a deposit at 3pm and people came to pay after 3pm). Is this possible?

I am trying to create a template to calculate the expected number of days for my shipment to arrive and I am cracking my head trying to put in the day as well i.e. for today [Aug-5-2002 Mon] How do I format it?

Anyone can help me? Thanks.

(btw, is it possible in the first place?)

Right click the cell and select format cells, select custom from the list and put in mmmm-d-yyyy dddd and the resulting date will be in your format.

Excel 2003a = shift (range name)b = 10/24/10 (cell reference)c = 10/30/10 (cell raference)need to count shift with occurance in Date Range 1 if >=b or <=c or Date Range 2 =if >=b or <=cSame date within to seperate range names.Cannot seem to get this to work using SUMPRODUCT or SUM array. Assuming it is a syntax problem, but not sure.Have been struggling off and on for days. Please help!

Please review the How To referenced in my signature line and then post an example of your data.Include some input data and the expected output based on that data.With a better understanding of how your data is laid out we should be able to offer a solution.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesI want to calculate how many days there are between two different dates and have it as a whole numbere.g.in the cell I type(where A1 is 17/06/03 and A2 is 13/06/2003)=A1-A2I get a rather large number whereby I was expecting a value of 4 !!All help welcomeLouise

Does anyone have an Excel spreadsheet that calculates someone's age (in years and months) from the date of birth, please?

I need a formula that will calculate the hours on shift. eg. start at 10.30pm finish at 2am total hours=?

I have done it and will now look for it for you.

3 more repliesI have two sets of date/time readings, each in three colums with drop down menus for date, hours and minutes. I need to subtract one date/time from the other to give a time difference in hours.

Hi,You haven't said what cells any of your data is in, or what format your date / time values take.This suggested formula is based on the following:1. Start date is in cell A2 and the date is recognized by Excel as a date (for example it will accept different date formats, and if formatted as a number will show a value around 40000 if it is a recent date)2. Start hour is in cell B2 and it is an integer in the range 0 to 243. Start minute is in cell B3 and it is an integer in the range 0 to 594. The Ending date/time values are in cells D2, E2 & F2As you want only a difference in hours, rounding has been applied, based on 30 minutes or greater rounding up to the next hour.Enter this formula in any empty cell:=IF(MINUTE((D2+TIMEVALUE(E2&":"&F2))-(A2+TIMEVALUE(B2&":"&C2)))>29, (D2+TIMEVALUE(E2&":"&F2))-(A2+TIMEVALUE (B2&":"&C2))+1/24, (D2+TIMEVALUE(E2&":"&F2))-(A2+TIMEVALUE(B2&":"&C2)))Format the output cell:Apply this Custom number format [h]The square brackets are required. Excel will now convert days of difference to hours of difference and add it to the time difference.If the assumptions given above are wrong, please provide the appropriate details.If the formula does not return the expected number of hours, please post all 6 input values and the result of the formula.Regards

5 more repliesFolks,though I an good with numbers, I don't usually deal with financial things...I'm trying to either find a calculator on the internet, or use Excel features to work out how much interest would be earned on regular payment.I can do it in Excel, "longhanded", making a chart that slowly progresses, but I'm sure there must be a formula to do what I want.The problem is that all the financial stuff in Excell is either US based, or using terminology that I don't really understand...Can anyone advise on what formula to use, either build in or created, that will work out the following;Start amount =£wMake a monthly payment of £xAnnual Interest Rate of y%Interest calculated daily, accumulated, and then paid annually.Number of months = zThis must be doable easier than the method I'm using... I'm having a mental blank!Thanks in advance.Regards,Simsy

Hi Simsy, I find your post slightly confusing. On the one hand you speak of interest earned, and in the body of your post you mention payments. My assumption is, that you have savings of a certain amount which you add to on a monthly basis and want to monitor the interest earned. If this is the case, I can send you an Excel spreadsheet via the yellow envelope, which I personally use for the same purpose.Abel

2 more repliesmy excel isnt calculating correctly. i know it sounds odd but its true. im doing some simple spreadsheets too. one is a payroll sheet, the other one is account receivables. to make this short, i have an open bill for 13.33, i got paid for it and i jotted down the info in the next cell, which was 13.33. but, excel tells me that im short a penny. i dont know? even my wife whos an accountant and uses excel everyday is clueless. please help

How was the original 13.33 calculated?If it was the result of a formula and there is rounding that is caused by only displaying 2 decimal places, that could be your issue.Keep in mind that even if Excel only display X number of decimal places, the actual values are still stored internally.Try this:Format A1:A3 as a number with 2 decimal places.In A1 enter 1.652In A2 enter 1.654They will both display 1.65.SUM what you see with your brain and you'll get 3.30, right?Now, in A3 enter =SUM(A1:A2) and you'll get 3.31.Is that off by a penny? No, since 1.652 + 1.654 = 3.306. Since all three cells are formatted to display 2 decimal places, A1 and A2 round down to 1.65 but A3 rounds up to display 3.31.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more repliesCan anyone help with a problem Ive been given and that is I want to calculate the depreciation of equipment (office machines) and I dont know the formula to use in excel.

Type Depreciation into the help. There are a number of functions there.

5 more repliesHello,

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!

I am creating a time sheet in Excel for a 24 hour shift schedule. Example: Start time is on Day 1 at 07:00 hours, End Time is on Day 2 at 07:00 hours. I have used this formula to get the hours worked,=IF(F17>H17,H17+1-F17,H17-F17)*24The problem is the target cell shows 0.00 because it's 24 hours. I would like it to read 24.00.If the times change then the cell reads properly; (07:00 to 06:59 = 23.98 hours)Any help would be great.

If you are using separate Date & Time cells I would advise against it.Combine both Date & Time in one cell, it will make your life a lot easier.What your doing now is subtracting 7:00am from 7:00am which of course equals zero.That's because the formula does not know that one 7:00am is on a different DATE then the other 7:00amIf you must keep the date and time seperate, then modify your formula tocheck for the date first.If your data looks like:

E F G H I

17) 6/8/2012 7:00 6/9/2012 7:00

In cell I17 use the formula:=IF(G17>E17,H17+1-F17,H17-F17)*24Note that we check the DATE cells to see if weneed to add a day.Format cell I17 as General or Number.See how that works.MIKEhttp://www.skeptic.com/

Hello,

Had another thread and couldnt find a solution to my problem. However in the course of working through potential solutions I discovered the larger problem. The Date() function stopped working in my access 2003 database. It was working before (which means the refrences are good) but now if I use it in a form field (in some fashion) i return an error, if i set it as a default value in a table the table errors out when I attempt to save....etc.

Did research for a day on the net and found this problem wasn't a figment of my imagination or unique to me. I found posts on help forums dating back to 2004 describing how "Date() just stopped working...", but in every case the response was always "Check Your Refrences" which never actually solved the posters issues (see my earlier statment about the refrences) and then you get the crickets.

P.S. While researching the issue I ran accross a string of posts where several folks tryied to help someone on the TGS forums with a similar issue. In both cases the Now() function continues to work but Date() doesnt. In that case it was solved because the user could accept the functionality of Now() as it records the time in the table cell.

So I'm hoping for better luck. Anyone run accross this or a solution on how to restore the functionality of the Date() function? This has been really frustrating.

Specifically I am trying to use it as a defualt value in a form for my database. The Now() function works jus... Read more

So I solved my own problem here. So if anyone runs into this themselves or if your just plain curious here is the problem.

It has to do with a versioning issue and installations of Access 2003. So if you create access 2003 on Machine A and then move it via whateve method to Machine B you can run into problems. If Machine A has any slightly different installation issues with the Refrences your hosed. Now why this only effects some code commands like Date() and Term() but not Now() you will have to find someone a whole lot somarter than me.

The simple solution is simply open up refrences and deselect any refrence that has the tag "MISSING" in front of it. Once this is done the missing refrences will not cause these code commands to error out and Date() or Term() should work fine.

P.S. Oh and once this issue was solved the Default Value in properties solved my previous issue with Data Entry causeing Date() to stop working. The versioning on access and the refrences can cause a cascading failure so the problem gets worse and worse over time. Case in point Date () works in all but a certain instance and moves to a state where it doesnt work in any instance.

Hi there,

I have a dbase created in the UK and I am storing dates in my database and I am getting a funny result when I perform a search and filter on from and to dates.

I have the following data:

Car 1 from 01/01/2006 - 30/01/2006

Car 2 from 01/02/2006 - 28/02/2006

Car 3 from 01/03/2006 - 30/03/2006

When I do a filter to report on cars from 01/01/2006 - 20/01/2006 only the first row is retrieved, however if I redo to the search from 02/01/2006 to 20/01/2006 expecting again only the first row. I instead get car 1 and car 2 reporting back as a match. It seems to be swopping the month round for some reason!?

If anyone could help me I would be eternally greateful.

I cannot for the life of me get this to work. I attach the code below:

On Error GoTo Err_report_Click

Dim stDocName As String

Dim stWhere As String

If CDate(searchstartdate) > CDate(searchenddate) Then

MsgBox "Please Enter an End Date Before the Start Date", vbCritical + vbOKOnly, "Report Error"

Else

stDocName = "DriverHistory"

stWhere = "driverNo =" & Me![driverNo]

' Check if we have a search start date

If Me![searchstartdate] <> "" Then

' This clause will be ANDed to the existing clause

stWhere = stWhere & " and "

' now check if we have and end search date

If Me![searchenddate] <> "" Then

' We have and end search date so we need to bracket the whole expression

stWhere = stWhere & "... Read more

Hi there,

I have an excel 2003 worksheet that collects data from access 2003.

I've managed to import data from access 2003 query to excel 2003 but this will import all data from day 1 which I do not want (too much data).

I've create a worksheet that contain only 2 dates (StartDate & EndDate) and using access 2003 to link the worksheet.

Now this is what I wanted to attempt: using the link table in access 2003 to automatically update the query (there is only one column with date) so that when I refresh my data in excel, it will import the data based on the dates I've specified in my worksheet containing the start and end date.

My question: How to let access 2003 automatically update the query table without prompting me for the start and end date? (Tried using Between [Forms]![Tables]![fmWhatDate]![StartDate] And [Forms]![Tables]![fmWhatDate]![EndDate])

Hope the above is understandable. (Do note that I know nothing about VB codings)

Thanks in advance.

Hi,

Trying to write some code that checks the DateDiff between 2 fields once the 2nd date is entered. This is the code as it stands...

Dim Days As Integer

Dim msg As String

Days = DateDiff("d", [Agent Notified], [Collected])

msg = "Date Difference is " & Days

MsgBox (msg)

I tried placing the code behind the "On Change" event but it tries to run the code after each digit of the date is entered so I placed it behind the "Before Update" event. This seems to work better apart from if a date is entered in error which is then deleted. I then get a "Invalid Use Of Null" message.

Question 1: Is the "Before Update" event the place to have this code?

Question 2: What is the "Ignore if null" code?

TIA

SBF

Hi All

I need a criteria within a query to return the last 12mths data from a date field, it must return 12 mths data from the most recent date in the database not from todays date.

All help is appreciated

Cheers Brian