Hi

I hope somebody can help.

I am attempting to set-up a spreadsheet to workout calculations based on working days (not Saturday & Sunday) only.

I can work it out using Saturday & Sunday, how do I exclude weekends from the calculation?

Thanks

Dave

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

use networkdays()

or

workdays()

see

http://office.microsoft.com/en-gb/excel-help/networkdays-HP005209190.aspx

http://office.microsoft.com/en-gb/excel-help/workday-HP005209339.aspx

I have a query regarding calculating ages and using the result in theVLOOKUP function.I have 2 dates, both entered manually, one is a date of birth and the otheris a date of a cycle race. I then need to refer to a table of standard timesfor given ages at specific distances. I have got the VLOOKUP functionworking o.k. but I can't get the date calculation to work so that when theDOB is earlier than the current date it gives the correct age in wholeyears. The calculation seems only to take into account the year and not theday and month.My table of ages, distances and standard times have the ages in whole years.I have used the formula =Year (A1)-YEAR(B1)-1.I have also used the formula =YEARFRAC(C4,$D$1,1)Can you make sense from the above or do you need my worksheet?Many thanks,

I am trying to calculate the number of days in each specific fiscal year from the new release date up to but not surpassing the original release date. Original Release Date - 5/26/2010New Release Date - 2/01/2011SFY10 - ends 6/30/10SFY11 - ends 6/30/11I can find the total number of days from 2/1/11 but cannot figure out how to calculate them between each fiscal year and stop it at the original release date.

Hi,I had this: A B C

2 Original Release Date 26/May/10

3 New Release Date 01/Feb/11

4 SFY10 ends 30/Jun/10 35

5 SFY11 ends 30/Jun/11 216

The formula in cell C4 is:=IF(B2<EDATE(B4,-12),B4-EDATE(B4,-12),B4-B2)The formula in cell C5 is:=IF(B3>B5,B5-B4,B3-B4)The reason the formulas are longer than you might expect is to cope with the situation where either or both of the following occur:1. The original release was before the start of the prior fiscal year2. The new release date is after the end of the current fiscal yearHere is an example: A B C

2 Original Release Date 26/May/10

3 New Release Date 01/Sep/11

4 SFY10 ends 30/Jun/10 35

5 SFY11 ends 30/Jun/11 365EDate is a function to get the 'same' date a certain number of months before or after the given date.Regards

I have a chart whose title I would like to read "Between (Date 1) and (Date 2)" The Date 1 field, AD4, and the Date 2 field, AF4, changes depending on what date range I want to use for data. I looked on a previous post, http://forums.techguy.org/business-applications/601180-dynamic-static-content-excel-chart.html, which led me to put in the formula bar: ="Between " &$AD$4 "And " &$AF$4. Excel gave me an error, so for now I'm just trying: ="Between " %$AD$4. When I do this, the field displays the date as a serial number (ex. 40544) instead of the date. Any ideas on how to fix this and be able to put both the before and after in the title?

I'm running Excel 2010 on Windows XP.

Thanks,

Dobber92

What happens if you link the title to a cell containing:

="For "&TEXT(AD4,"dd-mm-yyyy")

?

NB: this response is to your pre-edit question.

Hi everyone, i just ran into a little pickle. so i figured i'll earn some brownie points by going the extra mile on the new template i'm making for work, turns out it's harder than i thought it was. so, my current formula in my conditional formatting looks something like this =(networkdays(TODAY(),IF(A2=0,A1,A2))>365). in A1 i have 3/21/2010, A2 is blank. what i'm trying to achieve is that, if a year has passed since the date in A1, then the cell pattern colour would change to red. the IF function is there because i want to take the value of A2 instead of A1 if there is a date in A2, and if that is the case, the conditional formatting in A1 is void, and wouldn't change colour regardless.

i've tried disecting the formula i wrote, and i find that the "IF(A2=0,A1,A2)" always generates the serial number that represents a specific date, and not the date itself.

Anyways hope i can get some help on this. thanks all.

Hi, I've got something that keeps going wrong.\

I have a large file which I use at work to register and plan my colleague's tasks and resgiter (outside our regular vacation/sick/abscence, etc.) the different calls they are working on allocating this. But that works, that's not the issue.

I have a sheet named "Medewerkers", yes it's Dutch for Employees of Colleagues.

I extracted and stripped this portion. (June 16: removed this attachment, correct attachment in final post)

On this sheet double clicking in Column A on the name will open a userform to edit this one or if you double click the first empty row below it you get a blank form to enter the data.

Since I regularly ran into problems useing the ....ocx calendar picker I wrote my own date picker and it works. Using son supporting code I found the funtion GetDateInLocalFormat() does it's job.

But now the issue:

The person on the sheet has as starting date 01-03-2012 (in Dutch format this March 1, 2012) and ends August 2012

On my system and at work The date pickers selects it correctly, show is and the userfrom shows it as 01-03-2012 but when I press 'Vastleggen" (Commit) the date is saved as 03-01-2012 which in Dutch January 3, 2012 when I double click again the datpicker shows the default data as January 3, 2012.

The cell's format is dd-mm-yyyy;@ the system's settings are for code 31 which is Dutch and I have pasted screen shost of the settings that show this.

I... Read more

I changed my code a littel and instead of passing the date in local format I pass it as a serial date and then there is no doubt and the result is correct. But if somebody can offer a better solution, I'm all eyes and ears, and ... maybe it will help others who struggel with date values and the international settings

I'm working on a spreadsheet that lists monthly recurring expenses. I have one cell on the sheet listing the current date (using the "=Now()" formula), but I'd like to have the other cells change at certain times throughout the month.

For example, if one cell currently says "April-15", when the actual date reads "April-16", the respective cell would change to "June-15". This way, whenever the spreadsheet is viewed the next recurring payment date will be seen.

I know this is the wrong tool for something like this - I'm using SharePoint at the office with a calendar list with workflows that shoot off emails in a much better put together format - but this is just for home use and I'd like to keep it all in one document if possible.

Any ideas or suggestions would be greatly appreciated. Thanks in advance.

The way I read it was that you have fixed recurring payment dates, so you just want to know the next date based on the day today. Hope the attached might assist.

You need to list the payment dates somewhere and then the formula looks up the next date. You might want to look up today plus 1 (or something) to reflect the fact that if the next payment date is today, there's a good chance you'll not get any unplanned payments on that run.

How do I find the average using time and number, assuming that the time is in hours, minutes, seconds?

Hi All,

If anyone could offer any assitance i would be much appreciated! I'm ready to pull my hair out!!!!!

I have a excel document that has field with 25+ digits. What I want do is mail merge these fields into word.

Only thing is I don't want a 25 digit figure in my word document, 7 at most. I'm fine with the mail merge process but I can't seem to get the number down to a useable figure in excel that I can merge. Even if I get the Cells to display on 7 digits, when I mail merge it takes the full 25 Digit number from the cell.

Is there way I can tell the formula that calculates the 25 digit figure to only return the number and stop at 7 digits? Or is its something I have overlooked after thinking to much into it!

I hope that makes sense.

Thanks in advance.

I am frustrated with the following problem.

I need to convert the date to US formatting.

I receive a file in .csv format and the dates come as text and a normal date format all within the same column.

Example:

12/4/2012 23:16

12/4/2012 23:21

13/04/12 12:04 AM

13/04/12 12:52 AM

13/04/12 3:20 AM

The top 2, Excel reads as date. The bottom 3 excel reads as text...I need the bottom 3 to read as date.

Please HelP!!!

Hi,

It's me again trying to teach myself Access.

I need to create a database that allows calculations on the fly. I thought to do it Excel and have the Excel worksheet somehow in the form.

I was able to bring the Excel cells into Access, but the calculations don't work once it's in Access. Is there anyway to do this?

Additionally, I see in Help something about MS Access Forms being used to enter data into Excel. I cannot get this to work. I went through add/remove features and made sure everything was installed. In Excel when I click on Data there is no MS Access Forms option. Does anyone know about this? How can I get it to work?

You all always have the greatest suggestions and ideas. I look forward to hearing them.

Thanks

I want to know an exel formual for the following - Any help would be great

Date Range

01/12/2013 - 03/14/2013

How many days from the date range 01/22/2013 - 02/21/2013 are in the above date range

So answer for the above would be 31

Thank you for your help!

Hi LucianC - try entering this info:

Start Date in A1

End Date in B1

=DateDif(A1,B1, "D") in C1

Hi everyoneThis might be an easy one for whoever knows itI have a number 8202014I need to format it as a date to show 08/20/14And the formula should also work for 10202014 to show 10/20/14I cant make a formula with left, mid, right that works correctly.Thanks in advance!!!

If you know that the cell will only contain 7 or 8 digits, you don't need the second IF clause. The logical_test will either be TRUE for 7 digits or FALSE for 8, so a single IF function is all that you need.This version is shorter and slightly more efficient:=IF(LEN(E10)=7,DATE(RIGHT(E10,4),LEFT(E10,1),MID(E10,2,2)),DATE(RIGHT(E10,4),LEFT(E10,2),MID(E10,3,2)))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more repliesI have a spreadsheet where I need a cell to calculate the date (from a cell in column B) plus 14 days to get a due date, but leave the due date cell blank if there is nothing yet entered into column B. What formula should I use for this?

Dates can be added just like numbers. Add 1, you've added a day; add 14, you've added 2 weeks.An IF statement can perform a calculation IF the logical_test argument is TRUE:=IF(B1="","",B1+14)If B1 is equal to nothing, return nothing, otherwise return B1+14.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesI have an excel spreadsheet with a date column. The column format is *3/14/2001. I enter 08/01/2011. The formula bar reads 8/1/2011. The cell reads 40756. If I put the cursor in the formula bar the cell reads 8/1/2011. Place the cursor any place else cell revers to 40756. How can I get the date to display as a date in the cell.

One of your options is messed up:Click on the Office Button in the upper left cornerClick on Excel Options, down the bottom of pop upClick on AdvancedScroll down to the section that reads: Display Options for this Worksheet:UN-Check the box that read: Show Formulas instead of their calculated results.Click OKThat should correct your problem.MIKEhttp://www.skeptic.com/

3 more repliesI have a date tab with dates (Column A "dd/mm/yyyy", Column B "MMM-yy" and Column C "yyyy-yy" [financial year]).I work in a hospital. I have a list of surgeries with the date ("dd/mm/yyyy") they were performed. I have a Vlookup to obtain the "MMM-yy" data (for my monthly pivot tables) as follows:=IF(ISNA(VLOOKUP($B39,Dates,2,0)),"",VLOOKUP($B39,Dates,2,0))I came across an error in my source tab - all dates in March 2017 had "MAR-16" in Column B. I changed these, manually, to "'MAR-17".The Vlookup continues to work every single month but for MAR-17.When formatted as General (as all the others are), it returns "42795". When I change it to Custom format "mmm-yy" (which none of the others, which work fine, are), it returns "Mar-17" but in the pivot table comes up as "1-Mar-2017" whereas all the others are "FEB-17" etc).Any help would be great.Thanks in advance!

I'm not near Excel right now to try anything, but my guess is that all the working "dates" are not actual dates as far as Excel is concerned. My guess is that they are actually text, which may explain why they don't show up in your pivot table with the "day" portion or display the serial number like the Mar-17 cells do.Excel can be weird when it comes to dates/text, especially if the "dates" were imported from another source. They often come over as text and is can be troublesome to convert them to dates that Excel will recognize. In your case, you may have the opposite problem. I think you have to change your Mar-17 to Text in order for your VLOOKUP to work like the others. That can also be troublesome because Excel will often be reluctant to give up a Date format once it is set. You could try adding a single quote before a few of your Mar-17 entries to force them to Text and see how Excel handles them.Like I said...just a guess.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more repliesHello there, I have been looking a while now without much luck. It may be that I don't quite know the proper search terms but my problem is this:the problem concerns apartments, their occupancy, and their last remodel.I am looking to create a nested function that will return "Need To Remodel" as the result if 'F14' is is unoccupied and was last remodeled before 2005 ('G14'). Otherwise, the function should return "No Change" as the result.Column F has yes/no values concerning whether it is occupiedColumn G has a date (2004,2008,2001, etc) giving its last remodel Any help would be greatly appreciated

re: Column G has a date (2004,2008,2001, etc) 2004, 2008, 2001 are not dates. If that is all that is in the cell, it's just a number according to Excel.=IF(AND(F14="No", G14<2005), "Need To Remodel", "No Change")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

9 more repliesI am trying to keep track of due dates on my spreadsheets. I have some dates that expire annually and the others biennially.

You'll need to explain what you mean by "keep track of due dates".For example, are you asking to have them turn a specific color when they get within a month of coming due? Are you asking for pop up messages when a due date is imminent?We need details, my friend, details.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more repliesin excel when i type a date ie..06/10 it shows as 9/1/1901 i have tried changing the format cell but that doesnt fix it. is there another format or formula i need to find to fix this

Where are you located, US or Europe/Asia?Is your default date: 12/31/2011 Month Day Year ?or 31/12/2011 Day Month Year ?I do not know how you are getting 9/1/1901 from entering 06/10,but you can try a Custom Format and see how that works.Custom FormatRight click on the cell with your DateSelect Format CellsSelect Custom, at the bottom of the popup windowIn the small input box, under the word Type:delete what ever is in the box and enter:MM/DD for Month Day type of dateorDD/MM for Day Month type of dateClick OKAs long as your cell contained an actual date then you should see it change.MIKEhttp://www.skeptic.com/

2 more repliesAll my Excel 2010 files on Win 7 64 bits have had their date formats changed from yy-mm-dd to yyyy-mm-dd; formats that were previously yyyy-mm-dd have been left unchanged.This is very time consuming as I have to do format cells changes on every field of every tab of every file affected; because of the space factor, I need to use the shorter format.I have woken up after spending hours on manual format cells changes and have thought of checking if my short date format in regional date formats had been changed...sure enough; I have made the correction and everything is back to normal.The change must have been made by Windows update between 13/3/22 and 13/3/25; the only time I change formats is when I install a new version of Office (October 2010 in my case).

Since everything is back to normal, I assume this is just an FYI, correct? I didn't want to leave your post as "Unanswered" so I am posting this response.DerbyDad03Office Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more repliesExcel 2010 IF stmt based on DATE value: I have a simple employee expense report that calculates mileage in cell D12 based on the date in cell B12. My formulas is as follows: =IF($B12>=DATE(2011,1,7),$D12*0.555,$D12*0.51). All mileage incurred equal to or greater than 07/01/11 should be calculated at $0.555 cents while all else should be calculated at $0.51 cents. I believe it's as simple as Excel not recognizing my DATE(2011,1,7), as the date 07/01/11... Help?

You are right, Excel will not recognize DATE(2011,1,7) as 07/01/2011 sinceDATE(2011,1,7) means January 1, not July 7.The syntax is: DATE(year,month,day)=DATE(2011,1,7)=DATEVALUE("1/7/2011") will return TRUE.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesI have a simple spreadsheet TBH for which two columns of data have date fields:

End date and Reminder date

I would like to set a conditional format using the icon sets for the reminder date as follows:

1) If the reminder date is less than 90 but greater than 60 days of the end date then show me a green icon

2) If the reminder date is less than 60 but greater than 30 days of the end date then show me a amber icon

2) If the reminder date is 30 days or less than end date then show me a red icon

I have come across ways of using today and dates in advance but not before...please can someone help me.?

Hi

To use the icon set you'll need to calculate the differences in a new column in the spreadsheet and then apply the conditional formatting to the values in that new column. So if your Due dates are a Col A and your reminder dates are in Col B in Col C use =A2-B2 and drag this down to the end of the list. Then apply the conditional format to Col C.

Here is an example spreadsheet.

In excel 2010 new sheet, I would like to enter order number on column A1 then column B2 will show time and date automatically then after entered data then A1 and B1 should locked the cell and won't change anymore. Is this possible to do it?Thanks

If B2 will be empty the first time an order number is entered, then this code will enter the date and time once and will not change for subsequent changes to A2.If there will already be something in B2, then things get more difficult and I will need more specifics.(BTW...I am assuming the A2 and B2 are just examples. The code actually applies to the entire Columns A & B)

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if change was made to Column A

'and respective row in Column B is empty

If Target.Column = 1 And Cells(Target.Row, 2) = "" Then

'Unprotect Sheet, Enter Date/Time in Column B, Protect sheet

ActiveSheet.Unprotect Password:="zzzzz"

Cells(Target.Row, 2) = Now

ActiveSheet.Protect Password:="zzzzz"

End If

End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I'm trying to create a formula that will input the maturity date of a particular loan. The spreadsheet contains the interest (fixed rate), the monthly payments, and the current balance of the loan (see below).

The current date will be displayed at the top in a separate cell that we can use to base the maturity date off of, but I guess I just don't understand how to do the math to begin with.

Eventually, I'd like to add a separate sheet with the amortization schedule which could easily just be referenced on this sheet instead of doing all the math here, but if I can understand the math first, I can take it from there.

Any ideas? Thanks in advance.

Hi, I was just curious to see how many Excel posts have gone unanserede and cam accross your post.

I'm not in to economics or finacila calculations, but Excel does have a function named EDATE()

description and syntax is described in the link below:

http://office.microsoft.com/en-001/excel-help/edate-HP005209073.aspx

Amorization table in ExceL:

https://www.google.com/?hl=en&gws_rd=ssl#hl=en&q=amortization function excel 2010

Maybe this will help you along.

Hello, i have a quick questions. Im building an access database and i need to do some date calculations. Here is the break down of the form

Form

textbox1 = Warrenty_Date_expired - Date data type

textbox2 = Shipped_Date - Date data type

combo1 = warrenty_length.

The combo is a number 1-10 in years.

What i need to do is select a date from txt2 and then select a number from the drop down list and have it add it display it in txt1

so it should look like this

txt2 = 4/9/2009

combo = 1

txt1 = 4/9/2010

so its basiclly adding 365 to the date or 730 if 2 is selected and so on

I have no clue on how to do this. I do know some programming, but for the life of me i cant remember on how to do this. I know i need a variable and need to parse it and all, but i cant remember how....HELP me please

The DateAdd function may get you what you want...my code...

txt1 = DateAdd("yyyy", 1, txt2) or

txt1 = DateAdd("yyyy", Combo1, txt2) should work haven't tested

The "yyyy' indicates year

I have a time comm and time end field and a duration field that shows duration: (time end-time comm) and that displays the hours and mins.

When I want the following hours calculated: time comm: 23:00 and time end: 02:00 this shows 21 hours. Why does it not show 3 hours. Do I need to specify something in the time end field which tells it automatically that the time end will be ggreater than time comm?

thanx in advance

Help

I'm mail merging with Word a huge contract document and need to put in the phrase 'this contract is made on the [seventeenth] day of [April] [2010]'. Even in a custom format I'm struggling. When the contracts are printed off they need to be 'today's date' ie the day they are printed.

Thanks

I haven't been able to quite get my functions right on this one and am hoping for some guru help.

I have a spreadsheet with dates in Column A (Contract Received) and Column C (Contract Filed). Not every cell has a date in one or both fields, as it is a tracking tool for work in progress. However, a contract must be Received before it can be Filed, so C will (er,should) never be blank if A is populated. I want to know the number of days lapsed between date Received and date Filed, and if date Filed is null, then number of days from Received to today's date. If the contract has not been Received, then no calculation should be performed. In spite of requests not to, users sometimes put text notes in these fields, so would be nice to ignore text. At bottom of column, I need to get an average of the time lapse on all filings to track productivity. This doesn't sound so difficult to me, but somehow I haven't gotten it quite right in the last two hours and it's time to ask an expert. Thanks in advance for your help!

- k

Hi k, welcome to TSG.

Try this and let us know if it gets you close to what you need:

=IF(ISNUMBER(A1),IF(ISNUMBER(C1),C1-A1,TODAY()-A1),"")

It basically starts with column A and determines whether there's a number there (a date, as opposed to text or blank). If not, it doesn't do anything, but if so, then it does the same thing with column C. If C has a number (date) then it subtracts A from C. If not, it subtracts today's date from A.

Let me know if I've misunderstood the question.

Yo folks,

Annoying little thing in excel,

I want to show the date exactly like this: 00-00-0000 (08-03-2012)(dd-mm-yyyy)

Instead excel removes the zero's like this: 0-0-0000 (8-3-2012) (d-m-yyyy)

Is it possible to keep the zero's?

Thanks in advanced,

what i think you want is to format the cells with a custom format...

Use dd/mm/yyyy for the Type

edit: use dd-mm-yyyy for the Type

I am creating a database to store details of when particular properties are empty of not - this means that the 'date vacated' field is often empty (because someone is living there!). Although I can use the date() instruction to calculate the number of days a property was occupied as of today,I need to be able to calculate date values at a specific date (year end, month end, etc) Although I can obtain these figures by the simple expedient of re-setting the PC Clock, for obvious reasons I cannot hand this over to my client!Can anyone help?Glyn Foster

in the Helproom where it will do better, and I'll move it there now.

1 more repliesHey 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!

I am using Excel for a small mfg scheduling process. I have a date & time in the same cell and can add or subtract no problem. The problem is, our process shuts down from about 5 PM until around 11 PM during the week. I'd like to have my spreadsheet take the widget being made, and not complete at 5 PM, and add the estimated production time to stop at 5 PM and restart at 11 PM.

In other words, a job is scheduled to take 8 hours. We start the job at 1 PM. At 5 PM we're 4 hours into the job and I'd like to take the 4 hours left over and add to when we start back up at 11 PM and the worksheet showing the job is scheduled to end at 3 AM on the next day.

Then, to make it more challenging, how would we do the same thing over a weekend. I hope someone out there is smart enough to know how to make this "voodoo" happen.

Thanks,

I'm working on a date/time calculation to put on a MS Access form. My database field names are start and end and what I am looking for is the difference between the two. The start and end is a date/time format. I'm also interested in putting this in a report. Any help would be greatly appreciated.

I am trying to display ages in both year and month..but I can't seem to figure out how to do this. Any help?

I have a table in which I want to calculate Field 1 to pull all dates between 21-56 days. Does anyone know how I should type the expression?

Also, in the same table, I'd like to write a query to calculate a certain number of days between Field 2 and Field 3. I would like to pull all dates that are within 42 days. How should I type this expression? Also, where exactly in Design query should I type this expression? I know it would go under criteria, but under what field?

Lastly, I would also like to run a query that will calculate the number of weeks/days between Field 2 and Field 3.

If anyone has any tips on how to create calculated date expressions, I would very much appreciate it.

Thanks in advance for your input.

I see no one answered my previous question, but that's okay because I figured out the correct criteria.

Now I have another problem. I have several tables in my database. Table one lists a lot of information related to deliveries. My primary key in this table is SSN. Table two lists demographic information with the primary key being Sample Order. There is a field in table two called SSN that I've linked to table one. I am trying to add the "DOB" and "Sample Order" fields from table 2 to table one. However, table one does not have every record that table 2 has. Table two has 493 records whereas table one has 392 records. Table one has everyone in table two so I figured I should still be able to link the two records so that I can create query that will add the "DOB" field and "Sample Order" field to table one. Is this possible and how? I really would appreciate help on this issue.

Thanks.

Good afternoon:

At this time I am developing an access 2000 database, in which I need to make calculations with fields of the date type with the format of short hour.

For instance:

1. I intend to calculate in a third field the difference between 2 fields: final hour - initial hour ( 15:15-12:00=3:15);

2. Sum of working hours for a period (19:00+20:00=39:00).

I thank at once the released attention.

Best regards,

José Leal

joseableal@clix.pt

HiDoes any one know how to do hours and minutes calculations in excel. I have to work out the take up of available time. For example we have a library open for 9 hours 30 mins a day, it has 8 public access PCs so I need 8 X 9:30. Then I have to work out how much time was used and what that is as a percentage of the total time availabe. I have to do this for 26 libraries and then get a grand total at the end so I can put the figures in to one box as a percentage

You can do calculations with times in Excel as if they were numbers (they are stored internally as numbers, 1 = 1 day). So with 09:30 in A1=A1*8will give you 8 x 09:30.The problem is that the result will show as 04:00 because Excel displays a Time and not a Duration by only displaying the decimal part of the number. You can get around this by setting a Custom format for the result cell of [h]:mm and the correct result (76:00) will be displayed.

7 more repliesi have a rota spreadsheet in excel for time worked each day daily times in column for each day total for week end column can anyone tell me how to calculate wage by entering hourly rate and total time ie formula etc

These won't be where your figures are but try to adapt:A1 = total time worked, in Excel time format, Custom formatted as [h]:mm to correctly display durations of 24 hours and greater.B1 = £/hour, formatted as NumberC1 = wage =A1*24*B1 formatted as Number.

3 more repliesHello again FolksI've been trying to figure out have to do the following calc in excel ????Within a workbook there are severael sheetsI have column of figures totalled on one sheet and I want to copy that total to a summary on another sheet within the same workbook.I want to make that total on the summary page able to update the summary total as it the total accrues on the main sheet holding the original list and total?????? Phew!!!If you can understand that little lot I'd appreciate some advise please

Have you tried to use COPY on the total on the individual sheet then selct the cell you want that total to apear on on the Summry sheet right click then select paste special then click Link cells. this should make the cell on the summary update every time the total on the individual sheet alters

2 more repliesAny really bored Excel Math Wizards (Trig) want to take a look at this Topic ??

I have set up a form in Excel (2007) that requires simple addition of columns of figures representing cash. Although under normal circumstances this would be simplicity itself, it is made more difficult by the nature of the form, it is very desriptive and requires that the £s and ps be put in seperate columns. I'm sure that there must be a way to do the calculation but I have not come across this before and I'm having a problem, can anyone help please?Dave

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.

Problem in Excel 2010 [but revives a Closed TSG thread for Excel 2007]

The "Personal Macro Workbook" is a file called Personal.XLSB

The Personal.XLSB file is in the correct location /XLSTART/

The file properties option "Opens with Excel" is selected.

The file contains a (freshly re-made) macro.

(It also contained the same steps macro before it was deleted

and re-recorded during troubleshooting for this problem.)

When Excel is opened, the Personal.XLSB DOES NOT OPEN WITH it.

Because the Personal Macro Workbook does not open,

- existing macros do not function,

- nor can new macros be recorded

Same not-opening result, whether opened by clicking on

- Excel, in the Programs list or

- a desktop shortcut to a specific Excel file

However, the Personal.XLSB file can be opened and the macro made functional

by manually opening that file where it is stored in /XLSTART/

An unexplained workaround was identified in the closed thread.

*rename the Personal.XLSB to Personal.XLSA

I find this workaround effective.

The Personal.XLSA file opens with Excel and the macros are available.

But the file seems to be grayed out when viewed with Explorer in /XLSTART/

However, I do not like using workarounds, as they can cause trouble later.

I find that the "open with" setting was changed when the extension was changed

The setting is now "Opens with: Windows Shell Common"

I would appreciate an explanation how to make Personal.XLSB open as it should.

... Read more

I've searched for issues regarding non-working VBA code written for Excel 2010 and compatibility wit Excel 2011 for MAC.

I am currently using Addin (*.xlam) files that hold all the necessary code to process multiple files without needing to use the PERSONAL.XLSB and as eliminating the need to copy VBA code to every file that requires.

This works perfectly with all the Windows Office versions.

Today a colleague want's to run this same file on a MAC book with Office 2011.

The moment he opens the Addin het gets an error that a library cannot be found, understandable, but the Tools Reference in the VBA project is also greyed out so I cannot even try setting any reference.

Does anyone have any experience with this or is this something that needs a complete new approach when it regards Office on the MAC?

I hope somebody can help

Hi,

I've been asked to include dropdown choices in several colums on a worksheet which I did by using Data - Validation (cells D14 thru G14). However, I need to multiply each of these responses from these cells in the Total column ( cell H14).

Since I added the dropdown selections, the calculation doesn't work in cell H14. Is there a way to get the calculation for work when using dropdown selections?

I probably didn't explain this very well, so I attached the excel spreadsheet I'm working with. Please take a look at the tab called - "Safety Prioritization Form".

Thanks for your help

I'd like to find know how to calculate the compass bearing between two points defined by latitude and longitude. This would be over short distances so a simple rhumb line calculation, not a great circle route. Can anyone please help me with this?

http://www.movable-type.co.uk/scripts/latlong.html

I have an elderly neighbour who takes several tablets per day, but she tends to forget when to re-order a new prescription.

I have tried using Excel to do a calculation but I am having a problem when I update the spread sheet.

I have started by making cell D3 (Medication Stock) which is the current stock level at the start.

In Column C I have the date running down. In D4 I have the simple formula (=D3-2) 2 being the number of that type of tablet per day.

I have then conditional formatted the cells so that (a) when the stock level goes below 10 it highlight orange. And (b) when at zero highlights red.

However, when I try to edit the sheet by deleting the dates that have past, it comes up with an error, #REF!

Can anyone help with my problem, or suggest a better method.

Many thanks

DD

I suspect that you are deleting rows which would cause that error. Why not just hide them?

5 more repliesHi, Guys I built a table for random calculations. I need to kown how I make this to do automatic 1,000 times calculations and put these each calcutated numbers in a table

Welcome!Computer Hope is the number one location for free computer help. We help everyone with all computer questions.This sounds like a homework task. We don't do homework.If you will help us understand the importance of this task, we might be able to help you. You you trying to solve some kind of probability problem?

3 more replieson a worksheet i have figures in column AD which are entered to 4 decimal places, when other data is entered elsewhere on the worksheet, corresponding figures from column AD are shown in D3 and D4 these cells are then formatted with the figures being rounded up or down, the problem is that if i try to take these figures away from each other the result is incorrect(as rounded figures), excel is using the underlying figures with decimal places, how can i get the calculation to use the rounded figures

see below for example

column AD cell D3 cell D4 cell D5

55965.4798 55965 3081 55965-3081 = 52884, which is the result i want shown in D5

3080.6686

but Excel works the calulation out on column AD and shows the result in D5 as 52885(rounded up)

scouse13

Use the round function and round before calculating difference.

Try letting d3=Round(ad3,0), d4=round(ad4,0) d5=d3-d4

report back if any questions or problems.

Good Luck

I have a series of daily hours computed from the calculation:=sum(cellnx-cellny)How do I get the results to add up to a total number of hours?

This site offers 2 formulas, one for total hours < 24 and one for total hours > 24.http://office.microsoft.com/en-us/e...Does that get you where you need to be?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more repliesHi,I am using excel to calculate time sheets. I am using the formula A2-A1 and formatting the answer cell as[h]:mm and the answer gives me the time difference in hours between cells A2 and A1. Now I need to add cell A3 which contains .5 of an hour to that calculation to get my total hours worked. Sometimes cell A3 maybe .25 up to .45 of an hour. Does anyone know if this can be done in excell? Thanks

Have you tried:=A2-A1+A3/24Internally, Excel store dates as the integer portion of a number and the time as the decimal portion of a full day.Excel is going to see .5 as "half a day" or 12:00 PM so you need to divide it by 24 to fool Excel into using it as an "hourly" value.

3 more repliesWorking in Excel 2003.

I want to compare two sets of data with subtotals.

Is there a way to add/subtract/etc the subtotals?

If subtotal one is in C1 and subtotal two is in C2, then in whichever cell you wish to show the difference, enter +C1-C2 or +C1+C2

Can anyone help with adding (automatically) cells in excel....I'm trying to calculate litres to gallons which is litres (27.05 x 0.22) litres x0.22 gives the conversion to gallons...In this case it would be 5.95 gallons, then the mileage, say 119567, then the mileage taken away from the previous mileage to give a total breakdown of previous mileage.... then MPG, by dividing mileage breakdown by gallons....Price Lit Gall Mile B/Down MPG20.00 26.01 5.72 119647 0 0.0020.00 26.01 5.72 119847 200 34.95I will have to input the price and litres myself but need Excel to automatically calculate the rest... Can anyone help with this....thanks. EOS.

would be for someone to send you an Excel spreadsheet with the computation shown.But you would need to post your e-mail address.

3 more repliesWhy does calculation in Excel 2003 change from automatic to manual

One reason is that Excel will follow the "instructions" of the first workbook that is opened in any given Excel session.If the first workbook that is opened is set to Manual, all subsequent workbooks will be set to Manual for as long as Excel is open.Another way is an Open Event macro in a workbook that tells Excel to set the calculation to manual.There could be other ways, but I'd need more info before I could offer anything else.

11 more repliesOn excel, how can i get a formula to my working time. Let say my time in is 6:15pm to 2:15am because im working in night shift.any suggestion? Thanx

Use a DATE & TIME combination, not just TIME, that will make the calculations simpler.MIKEhttp://www.skeptic.com/

20 more repliesHi,

I'm using Microsoft Word 2010

Instead of typing in "today's date" every time.....

How do I modify the date macro I made for Excel so it will work in Word.

And where do i put it ?

Then I can have an Icon on the Ribbon !!

HTML:

ActiveCell.FormulaR1C1 = "=TODAY()"

Selection.NumberFormat = "mmmm/dd/yyyy"

Selection.Value = Selection.Value

mike

I'm working on timesheets, specifically TOIL (Time Off In Lieu). If an employee starts the year owing time, eg -3.30 hours, then works extra time of 3.30 hours, then takes another 1.30 hours TOIL I need a calcuation to show he owes 1.30 hours TOIL. At the moment I'm using this formula (because I have 3 months of time owed and time owing to calculate plus the brought forward 2010 figure) =TEXT(C21+D21+F21+H21+J21-E21-G21-I21-K21,"h:mm") which works for all employees who started the year without owing TOIL. However for those starting with a negative value I get the #VALUE! message. Any ideas?

The easiest way I have found to do negative time math is to convert everything to minutes, then you can use the minutes like they were regular numbers and you can do addtion, subtraction, etc.Instead of -3:30 you would have -210

A B C

Owed in

Minutes

1) 15:00 18:30 -210

The formula in cell C1 would be: =(A1-B1)*24*60MIKEhttp://www.skeptic.com/

I have a question to ask as underQty Slab Commission/bag Qty Amount Cummulative(Tonne) (Tonne) (Rs.) (Rs.)0-2500 Rs.17 2500 42500 425002501-5000 Rs.13 2500 32500 750005001-10000 Rs.11 5000 110000 18500010001-20000 Rs.9 10000 90000 27500020001-30000 Rs.8 10000 80000 35500030000-50000 Rs.7 20000 140000 495000>50001 Rs.6 7000 42000 537000Total 57000 537000How to calculate the Cummulative Amt as per different slabs achieved by an individual using if condition.RegardsRajesh H

Please click on the following line and read the instructions on how to post data so that your columns line up. Thanks!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesI URGENTLY need help on this I've got to complete it before lunch tomorrow GMT.

This is doing my head in!!

I want to display the difference of hours worked and hours budgeted.

Ok columns B = Hours worked C = Hours Budget D = Difference I've formatted a special with H:mm format.

The sum is obviously B-C

All is fine and dandy until The hours worked are less than than budet ie a negative number. Excel doesn't like negative time.

Easy I thought use an if and if the difference is less than zero do the sum the other way round ie C-B. Great I have a positive number which is correct - but they ALL look positive.

2 questions. How can I either:

1. Create a "negative" number so we can see the target wasn't met.

2. Format the results to be green positive and red font negative. Yes I tried conditional formatting and it didn't work.

Thanks.

when doing a spreadsheet using very large currency figures (over ten trillion pounds!!!), excel does not calculate exactly. it seems to round off. does anyone know how to force it to give exact calculations?

There is a limit to the number of significant figures that Excel can hold. I ca'nt remember what it is offhand. This is probably why you are getting rounding errors.

5 more replieshow do i calculate difference in time using a 24 hour clockI input a time in column one of 22:46:00 and in a second column input time of 01:03:00 and need to calculate the difference in hours and minutes.I have tried a text function and an If function but something keeps going wrong.

Ok, first of all make sure that the cells containing the data are formatted in time format mirroring what you have showing in your question (specifically, HH:MM:SS).Next, use this formula to calculate out the times:=IF(B7<B6,(B7+1)-B6,B7-B6)What this will do is check to see if the second number (01:03:00 in your example) is less than the first number (22:46:00 in your example). If it is, then it will add a 1 to it, essentially adding a day to the second number and allowing it to correctly calculate overnight time windows. Please let me know if you have any questions or need more assistance. -----IT Desktop & Network Consultant - MOS Master Certified, MCP, MCITP - Windows 7, CCNA Certificate Pending, A+, Network +::geek::

4 more repliesI am trying to create an excel spreadsheet where I am tracking my time between three different areas for each day of the week. Then add the time for each area and create the percentage of time spent each day and for the week on each of these areas. I am having a problem with the total of all the times.

I have the spreadsheet set up so that I am calculating the difference for each project/area. Then taking a sum of all the time for each area/day and setting that in a table. When I try to add the sums for each day for the total that week, it is not adding properly and I don't know what to do. If you highlight the cells for each day -- together, they add up correctly, but if I just do sum (a2:a5), it does not come out with the same answer. I also tried multipying by 24 but that comes out even weirder. Suggestions?

Copy of file attached. Look at cell H11 and H12 on both tabs.

I cannot help you with your problem...however there is a better location on this site for your question. The folks that hang out there, probably wont visit this forum all that much.

If I were you, I would click on the "report" button, lower right hand side of the post, and when the next page pops up, request that a forum moderator move your post to the "Business Applications " sub forum, located in the Software and Hardware Section of TSG.

BTW, please do not go to the subforum, and start another (duplicate) post, as that is against the rules here. I am sure you will find the help you need, people are pretty good around this site!!

I want to add a validation rule to a date/time field in Access 2010. This worked in 2003 and I'd like to know why it won't work now.

The rule is

Is Null Or <= Date()

I have entered this through the expression builder as well as typing it to avoid typos.

The error message is:

Unknown function 'Date' in validation expression or default value on 'Artists.Date edited'.

I am doing this at work: we are running Windows 8, Server 2012, Access 2010 via virtual desktop.

Any help would be gratefully received!

Thank you

Alison

Alison, welcome to the Forum.

I think you will find that this is an Access problem of Access 2010.

You may be able to overcome it by using Now() instead of Date().

If not I can provide you with some simple VBA code that will do the same thing that may work.

I have also seen advice to open a new 2010 blank database and import everything in to it to solve this problem.

Hello guys,

I have a report which has (User, Office, Problem, Date time open, Date Time Close, and Total Date time of job.) TotalDateTime calcultates the time from when it was open to when it closes.

The report shows the total of all jobs so i can print it out. At the bottom of the report i want it to tell me the total time of all the TotoalDateTime records, i.e:

Joe Bloggs Open-11/06/2011 10:30:00 Close-12/06/2011 11:25:00 Total- 1 Day, 55 Mintutes

Jack Black Open-11/06/2011 10:30:00 Close-12/06/2011 11:25:00 Total- 1 Day, 55 Mintutes

TotalOverPeriod - 2 Days, 1 Hour, 50 Mintues

I cant seem to make it total up...Anyone know how to do this?

Thank you

Somehow some time back I set Word 2010 to show a date on every saved page that I have. The date shows up on the bottom of the page on the left side. It also shows on a saved document for an envelope that I made. I want to delete that particular date stamp but when I open the envelope, I cannot figure out how to delete it on this item.

Any suggestions would be appreciated.

I recently built a new pc and I am using Windows 7. I work with a large number of jpg files. Most of the time I rename these files by using a date format such as 2010.11.08. I have noticed that when I use the date format in Windows 7--such as: 2010.11.08-- and hit the "enter" key the files are renamed: 2010.11 (1) etc and NOT the complete date (2010.11.08 (1) ---if I use a date format such as 2010-11-08 OR if I rename the files 2010.11.08 a I don't have that problem. This problem only happens when I rename a group of jpg files (more than one)?it does not happen if I rename only one file. I never had this kind of problem when I was using XP?Is Windows 7 different in this respect????

I have exhausted my FAQ files and have not been able to get an answer to this question.

Thanks in advance for your help.

At a guess - it is interpreting the last part of the date format (eg .08) as a file extension. However when you go to rename just one it ignores the extension.

Go to Start -> type Folder Options press enter -> go to View tab -> scroll down and untick the box beside "Hide extensions for known file types"

Then go back to the folder with the pictures in it and you may be able to see where the missing part of the file name has gone.

When items are loaned out we want them returned in 30 working days (approximately 6 weeks I'm not going to bother trying to calculate holidays) but to only come back on a week day Monday through Friday.

Field Name ReturnDate_Fld this needs to be calculated as 30 working days from the current date

or should I not worry about the extra effort and use the approximate by adding 42 to the BorrowDate_Fld (plus 42 days = 6 weeks)

Should I worry about this in a form or only set it as field in the report/query?

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.

Hello,

I have read through a lot of posts and have not come across one that actually fits my issue. I am trying to come up with a macro or a formula that will do the following:

Date

27-Oct-08

28-Oct-08

29-Oct-08

30-Oct-08

31-Oct-08

3-Nov-08

4-Nov-08

5-Nov-08

6-Nov-08

7-Nov-08

10-Nov-08

The date (the word Date is A1) is in colume A and the row is what is changing (ie A2 (27-Oct-08), A3 (28-Oct-08), A4 (29-Oct-08), etc) I don't want the weekends if possible. I have tried a formula but something is wrong with it "=DATE(YEAR(A2),MONTH(A2),DAY(A2)) - IF(AND(MONTH(A2)=2, DAY(A2)=29),1,0)" It makes every day (every row the same value, doesn't increament) The IF statement is suppose to check for leap year. The =Today() changes all the days to be the same, which I don't want. Can anyone modify this so that it works or auto puts the date in the next row?

Each row gets a new date, I don't want to have to hand type in the date everydat if I don't have to.

Thanks in advance.

Hi there, welcome to the board!

If you have "Date" in A1, and your start date entered manually into A2, you can put this in A3 and copy down...

Code:

=IF(WEEKDAY(A2)=6,A2+2,IF(WEEKDAY(A2)=7,A2+1,A2))+1

HTH

I have the following code

Code:

Dim EndDate As Date

EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)

If ws.Range("K4") <> "" Then

'Do Nothing

[B]ElseIf Date = EndDate Then

ws.Range("K4") = EndDate[/B]

Else

ws.Range("K4") = Date + (7 - Weekday(Date, 1))

End If

OK...since there have been no responses to me 2 previous posts, I hvae another question:

I currently have a formula (graciously provided by bomb #21) that calculates the number of years between the current year and a predetermined date.

However, it is based on the calendar year and not the current date. For example, between now and July 30, 2000 would be 10 years. However, the calculates still result in 10 years even when the date is changed to Feb 1, 2000 the result is still 10 years because the current calendar year has not changed.

Here is the formula:

YEAR(NOW())-YEAR(F6)

I need a formula that uses the current date as the threshold for the calculation. I've tried combinations of the DATE, MONTH, YEAR, DAY, etc, functions but can't get anywhere.

Anyone have any thoughts?

Regards,

TBaker14

there may be a better solution - but on first look, I would test the Month () and if month(now) > month(f6) then subtract one from the year calc - and then also do the same for Day if Month(now) = month(f6) not infront of an excel PC right now - but that was my immediate thought - but I'm sure there's a better way just using dates .

Hello, I am having difficulty getting Excel to sort by date with the following dates:

Column A

09/20/2012

09/21/2012

10/20/2012

02/20/2012

When I select "sort newest to oldest" it sorts the dates this way:

10/20/2012

09/20/2012

09/21/2012

02/20/2012

I have verified that the dates are not formatted as text and have altered the way the date is shown, trying it without the zero and with the zero and nothing changes.

Help!

I think I discovered my error, the dates showing 02/20/12 should be 02/20/13!

Hi guys,

Pretty new to Excel really, know the basic stuff but the intermediate stuff is beyond me for now. Hence this post.

What I want to do is this:

Have a field on my spreadsheet that contains a fixed date say 06/06/2008 and this cell would be green or a cell next to it would be green.

Now as the date reaches say 28 days in the future, so 04/07/2008 I want the colour to change to red.

Is this possible and if so, how do I do it???

Thanks,

Peaker.

Good afternoon all,

Long time no seek help!! (cos I learnt so much last time )

I have a worksheet containing a list of stock i.e. Col A = Date; Col B = Product Description; Col C = Amount of stock

How can I (hopefully using a macro) end up with a report containing the information in Col A; B and C but sorted/sub-totalled as follows :-

Stock older than 18 months

Stock held in store for 12 - 18 months

Stock held in store for 6 - 12 months

Stock held in store 0 - 6

Does this make sense - Is this possible?

Thanks if you are reading this !

Moll

does anyone know how i could change a column in a table that currently has dates in the m/d/yyyy format to mmddyyyy

here is an example

4/20/1991 to 04201991

this series of table contains hundreds maybe thousands of dates and I have to have the state report done by the end of the day any ideas would be appreciated

Thank You,

Andy

Dump it into Word and Use replace the / with nothing. Replace is gotten to via "CTRL-H"

I have an elaborate Excel file (a weight reduction program) that includes a cell that shows the first day of a month (dd-mm-yy). Based upon which month of the year is in that cell, I want to make an IF statement that uses the number of days in that month (28,30 or 31 days). If I copy that cell and format it as a DATE per the mm format, it just prints the first letter of that month. Therefore, if there was a "J" in that second cell, the IF statement would not know if it was Jan (31 days), June (30 days) or July (31 days).

Anyone have any ideas?

Thanks

[email protected]

Hiya

Moved you to Business Applications, as you may get more help here

eddie

I have a timesheet type Excel workbook. The week is official over on Saturday. When I open a blank timesheet, I would like for Saturday's date for each week to be automatically inserted. I also need a way for the date not to be updated, if the workbook needs to be reviewed at a future date.

Any help with this would be greatly appreciated.

Great!! I solve the problem also from DerbyDad03, smart you.After input date 29-Aug-17, it automatically changed into 0-Jan-00, the formula box shown =8/29/2017, but it won't affect the sort function. If try to priced with " ' " instead of "=" , it shown 8/29/2017. Follow your suggestion, "Turning off the Transition option" then, fixed.

More repliesHi 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.

My Challenge:

I have a table with 34 different date ranges representing individual rounds 1 to 34.

Start End Round

04/12/2009 05/15/2009 1

In another worksheet I have a long "to do list" of different tasks which have start and end dates. I would like to set this sheet up so that in a "Round" column, a formula would calculate which round each task is in, given its end date.

Task Start Date End Date Round

Final Material Review 31-Aug-09 31-Oct-09 0

I believe I may need to use either a VLookup or IF formulas, though from what I understand of IF formulas it would be far to complex with 34 different date ranges. I am not sure how to use a VLookup with date ranges in this senario.

I am trying to enter a date in a column as yymmdd but when I enter 09 as the year is drops the 0. I tried using different formats but that is not working either. For example, I changed the cell format to yymmdd and when I entered 090120 it displays 460926. Can someone tell me what I need to do to get it to display as I need?

Thanks!!

hi, the goal is to calculate the difference between the 'year' portion of 2 date entries in different date formats; example:

if cell 1 contains:

12/26/2000

and cell 2 contains:

31-Dec-11

what is the formula to get the result?

I am trting to create a chart using excel. I have inserted dates in column 1 - they are not sequential!. When I put the data into a chart it creates extra dates inbetween those I have actual placed in the cells. Help!

not a great solution - but format the dates as text - it should then only show the dates you have entered - and should be correct on the chart - but will be a number in the cells

not sure this is the correct way to go - but it seems to work

see attached

I'm using Excel 2000 and XP. I'm doing form letters and trying to merge dates that are formatted "20020721". How can I change the format to 7/21/2002 when merged?

Hi

I've searched several Excel sites, and I've found VB close to what I need, but I don't enough to make it work for me.

I have info in rows from A to J going across. Each row has information about a certain account. When anything in that row is modified, I'd like cell K to show the date modifed automatically.

I found a script that will do that but it only does A as the target cell and then puts the date in one cell over from the target. I need the target to be A thru J and the date cell to be K always.

Any ideas?

Here is the code I found on the Internet. Perhaps someone knows how to change it to do what I want.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Written by Barrie Davidson

If Target.Column = 1 Then

Target.Offset(0, 1).Value = Now()

End If

End Sub

Thanks

Hi, this appears to do it.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column > 0 And Target.Column < 11 Then

Target.Offset(0, 11 - Target.Column).Value = Now()

End If

End Sub

Excel gurus,

Back again with another difficulty...

I have the following imported data in a single cell representing the date time and a variable

A1

06/25/2001 15:27 1.4

With the formula =LEFT(A1,16) and =Right(A1,4) I am able to extract the Date and time from the variable from the cell as follows.

A1 II A2 II A3

06/25/2001 15:27 1.4 II 06/25/2001 15:27 II 1.4

What I am having difficulty with is transforming the Date and time (06/25/2001 15:27) to the excel sequential numbers known as serial values (ie 37067.64375). What I want is the following:

A1 II A2 II A3 II A4

06/25/2001 15:27 1.4 II 06/25/2001 15:27 II 37067.64 II 1.4

I have tried formatting the data to date or to custom m/d/yy h:mm AM/PM or m/d/yy h:mm without success. Any suggestions?

In Excel 2007, Is there a way show a date stamp of when data was entered into a cell? So when you hold the cursor over the cell it shows you the date the data was entered?

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

I have a user who is trying to update an Excel spreadsheet somebody sent them. In one cell, there is a reference to the date located in another cell. However, the way it is formatted, the date in the cell displays as just a number instead of the date ("September 22, 2010" displays in the 2nd cell as "40443".

In the second cell, there is some text along with the formula referencing the original cell containing the date. In the formula bar, it looks like this: ...Los Angeles, California 90013, on "&AP29&"

Yet in the spreadsheet, it displays like this: ...Los Angeles, California 90013, on 40443

In the original/refenced cell (AP29), the text entered into the cell is 9/22/2010, and is formatted to display as September 22, 2010.

Any idea how to correct the formula to display the date correctly?

Thanks a bunch.

Hi Traci.

It's because the underlying value of the date is a number, which is how Excel interprets it no matter how it's formatted. To change it to display the date, you have to tell it it's a date within the formula. Try this:

...Los Angeles, California 90013, on "&TEXT(AP29,"m/dd/yy")&"

Without seeing the whole formula in the second cell I'm not quite sure about the positioning of the ampersands and quotes, but play around with it a little. Let me know if it doesn't work.

Hi all, I have a crazy problem with date entry in my Excel 2007 & it's driving me nuts!!

I have two dates (departure & arrival) that I copy in from a text file. the values are 15/03/2011 & 08/04/2011. (DD/MM/YYY)

When I copy these into excel the 15/03/2011 is copied in "as is" but the other date copies in as 8/4/2011 and is right aligned. If I right click both cells are formated the same.

However if I view them as long date formats I can see that 8/4/2011 is now displaying as Thurs August 4th while the 15/03/2011 remains the same e.g. 15/03/2011

Why is the date 8/4/2011 changed to August instead of April and what do I need to do to make it stay the same format as the other date 15/03/2011.

I urgently need anyone who can help to reach out to me.

thanks

Ray

You are set for the wrong date format. Your machine is using U.S. style mm/dd/yyyy instead of the other format dd/mm/yyyy. The 15/03/2011 date is actually an invalid date the way it is set.

Start>Settings>Control Panel>Language and Regional Settings

Change the short date format or the Format section (change English to English - Australian or UK or whichever is appropriate.

Kevin

I use a lot of Excel macros at work, often formatting pre-fab reports for aesthetics, etc.

I'd like to be able to insert the current date as a part of the macros. I'm sure this can be done, but I don't yet know VBA well enough to write it out myself.

Any ideas?

I'm using Windows XP w/ SP2 and Excel 2003

See the code below. If you want to also include the time in addition to the date you can use replace the word Date with the word Now

Code:

Range("A2").Value = Date

or

Code:

ActiveCell.Value = Date

Regards,

Rollin