I'm trying to copy a date into successive cells in a column; re several entries happened on 07/08/2012. When I copy it it shows as 07/08/2013, 07/08/2014, etc in the successive cells? Help please!

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

It's probably a formatting issue. You can try to clear all formatting from the cells prior to entering a date and filling the series. If it's not a formatting issue, after you fill the series down, click on the small button in the bottom right of the range with a plus sign that shows up. You should have the option to Fill Days.

6 more repliesWhen I copied a range of date cells and pasted them into a new workbook, the dates changed by 4 years and 1 day. I found a solution on this site (posted by DerbyDad03) to change the date system, but I can't figure out how to do it in my version of Excel (Excel 2008 for Mac). Also, if the original workbook has the funky date system, how would I convert the dates to avoid this problem in the future?

Try this link for answers to both of your questions:http://support.microsoft.com/kb/214330Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more repliesHi all,

I am currently using Excel 2003.

I have a column of dates which display the following format 17-Feb-2006. I would like to copy theses from one cell to another, but not in a date format, but as text. Problem is, if I copy into another cell and change the cell format to text, it diplays the cell valuation of 38765.

I also try the data>table to columns route and it does show the cell as text but in this format: '02/17/06'

I believe in excel 97 I could simply copy>paste special>text only, but this seems to have been removed from excel 2003.

I have also simply tried to insert an apostrophe at the beginning of the data in the cell, but this also goes on to display the following format; '02/17/06'

Does this make sense and if so, can anyone pleeeease help.

Many thanks

I am trying to change some dates in a column in excel 2007. I want the date to read the month and year but the year that I need is 2007 and the cell wants to automatically change to 2008. How can I prevent this from happening?

Thanks for the help

puterputter

When I enter dates in the Uk english format (dd/mm/yyyyy) in Excel 2007, even though they are formatted to display dd/mm/yyyy, they are dispayed in the US english format (mm/dd/yyyy). The formula bar shows that the cell contains a date in the UK format as entered. The language setting in Excel Options is UK.

I wrote a macro containing an InputBox requesting a date, having previously defined the variable for this input as Date. The input is entered in the UK format but when assigning the value of this variable to a cell, it is displayed in US format. Not only is it displayed contrary to the custom format, but the content is rubbish. For example, when the date 31 October 2009 is entered into the input box in the UK format, 31/10/09, it is transposed to a date value of October 09, 1931 in the formula bar and displayed in the cell as 10/09/31.

Any ideas???

Operating system: Windows Vista

I have just upgraded from WinXP to Win7 and after reinstalling Office 2007 one key [for me] facility does not now seem to be available.

I like to input dates in Excel thus 28.11.11 but want the cell to show 28 Nov 2011. The regional settings in Win7 allow any format but I haven't yet found a way to get what I want. Spaces work as do slashes but I don't consider them to be as convenient as points (periods or full stops).

WinXP did have such a facility in regional setting but it has either been deleted or is just well hidden in Win7.

I do this through Excel itself. When you type 28/11/11 in the cell then I find it automatically converts to 28/11/20111. However to get what you want then on the hone ribbon click on the small arrow bottom right of number and choose custom. In the type box put dd mmm yyyy. If you highlight a whol column by clicking n the top before setting the format all of that colum will be in that format.

5 more repliesI have attached the example document.

Would like to know if i entered in a known date, how would i have it add in a known remaining years.

if the years calculate by a point and not a month.

Ex. there are 12 years in a month. there are only 10 whole numbers in the calculation.

so if the computer gives me a remaining life of 2.5 years that would mean two years and 6 months.

but what if the computer gives me 2.2 remaining years? how many years and months is that?

And once the 2.2 is figured out how can i have it auto calculate that to the known date that i have to give

me a total date with the computer date and the know date?

Let me know if the example is not explained well enough.

You don't need your chart to figure out the decimals. Probably the easiest way to do it is:

=B22+365.25*C2

This multiplies 365.25 (the average number of days in a year) times your half life to get the total number of days represented by the half life. Then add that to your date. It may be off by a day or two (depending on the half life) since some years have 365 days and some have 366, but it should be very close. Hope that helps.

Hi,

I have a spreadsheet that calculates how long a contract has left to run, using the following formula:

=((EDATE(R5,S5))-TODAY())/30

where R5 is the start date of the contract, and S5 is the duration of the contract.

I need to make the spreadsheet think that it's June, and not today, but I'm struggling. Does anyone have a suggestion?

Thanks in advance!

Answered my own question with a bit more fiddling. Just converted the date to a number and subtracted it.

I have an MI system that outputs a lot of information into Excel, I then need to sort the information as follows:

One entry will have multiple start and end dates for qualifications

For A27 I need to look at all of the A27 entries (1, 2, 3 etc) and put in the cell the earliest date.

For A28 A31 I need to look at all the A28 and A31 entries and put in the latest date.

I have attached a sample spreadsheet that will hopefully clarify the above.

Thanks, Lisa.

In the PT date list, 5/30/13 appeared after 7/31/13 in the column. Checked the source data and found that it was correctly formatted as 'dates'. The only thing I could do to get the May date to appear before the July date was to first change the source data formatting to 'General', which converted the dates to 5-digit numbers. When I refreshed the PT, 5/30/13 appeared before 7/31/13. Changing the source data back to date format and refreshing again put 5/30/13 after 7/31/13. This only happens to one date in the list, 5/30/13, i.e., the other dates are in the proper order each time I refresh. Any idea what's happening here? Thanks! Needless to say, it's a pain to have to do this workaround to get the PT to properly order the dates.

Hi tomt66, can you post something that exhibits the same behavior? Maybe take the spreadsheet you're working on, and blanking out everything except the dates and build that pivot table where the 5/30/13 is appearing after 7/31/13.

So I have a huge amount of data that are all projected visit dates that range over a course of future years. I want to pivot the data out so that I can relay back counts of Month/Year as needed to an individual text label. Everything I find online tells you how to pivot if you have data in a cell but not how to manipulate and work with dates in a cell instead.

Attached is my data.

I want to be able to use a pivot to tell me (for example) 'There are 68 visits occurring in January 2013 and those visits belong to site 1 (20 visits), site 21 (40 visits), and site 42 (8 visits)'. Or that it can tell me, for site 1, 20 visits occurring in January 2013 are for V2 (3), V5 (10), and V20 (7).

Any help is appreciated.

Using Excel 2007.

It looks like you don't have your table set up properly to get the pivot you're looking for. In order to do what you want, you have to have a column for Visit# and you would list your visit number there. Get rid of all of the other Visit columns. Then you have another column for date. Keep your columns for patient number and site number. Then you can create the pivot you want.

So basically, you want to convert all the visit columns (V2, V3, etc) to one column by patient and site, with the same patient listed many times in the same column.

The problem is, you want a pivot table that compares three values (three columns), Site, Visit#, and Date. But you have your dates separated across 52 different columns and that just won't work.

Hi,

I have created an excel spreadsheet of approx 20 pages. I want to copy it to a word doc. I've formatted the spreadsheet to show gridlines and keep the title row at the top of each page for printing. However, I cannot seem to copy the spreadsheet into word so it is as shown in the print preview. The gridlines don't show and the title bar is only at the top of the first page.

Please can someone talk me through the process of copying what I see in excel print preview into a word doc before I cry. I'm using Office 2007.

Many thanks in anticipation.

Phil

I have no idea if this is even possible, but I would like to create a spreadsheet that auto-populates a series of dates based on one date that the user enters. Let's say I have a goal date to complete a project, and for every project I work on, something needs to happen 6 weeks prior to the goal date, another thing needs to happen 4 weeks prior to the goal date, and something else needs to happen 1 week and 5 days before the goal date. I would like to set up 4 cells in Excel: one for the goal date, and three for the various things that need to happen leading up to that goal date. I would like to be able to enter the goal date into the main cell and have the other three cells auto-populate with the correct date based on what I enter in the goal date. For example, if I enter a goal date of 11/01/2016, I would like the other three cells to automatically populate themselves with: 09/20/2016 (6 weeks prior), 10/04/2016 (4 weeks prior), and 10/20/2016 (1 week and 5 days prior). Is this possible? Can Excel return dates in that way?

Sure, it's easy. Excel internally stores Dates and Times as numbers. Each day is a whole number and time is the decimal portion of that day. See here for a pretty good explanation of how Excel deals with Dates and Times as well as links to all the various Date and Time functions that Excel offers:http://www.cpearson.com/excel/datet...As for your question, simply subtract the number of Days required from the cell with the Goal Date.With 11/01/2016 in A1, use this to return 9/20/2016:=A1-42(42 is 6*7, or 6 weeks.)I'll leave the other two formulas to you. ;-)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesI have a Word 2003 form in which there are currency fields and date fields for the user to fill in. That's all fine, I can format the fields and calculate with the currency field to create properly formated currency fields that I generate.

On the last page I want to copy some of these fields. Currency ones are fine. What I can't do is copy a date field. I set the text field type to "calculation", and the expression to "=arrival" (the field I am trying to copy from is called "arrival"). Now I can't get the format right. If leave the number format field blank, I get a number that isn't anything sensible, and if I copy the date format from the original field into the new one I get nothing at all (it's offering me a number format, not a date format, so I suppose that's not surprising.)

This surely should be easy. What am I missing?

I recently reinstalled Windows, but not before making a backup of the entire hard drive, and now that I've got the system more or less working fine I want to copy the documents I have on my old drive. Problem is, using Windows file copy modifies the file creation dates - and I really don't want those to be changed.

I tried using Robocopy, but it kept complaining that the file location names were incorrect - apparently because the source folder has a space in the name... So I'm looking for another tool that can do the same without giving me headaches.

Can anyone help? Thanks!

If you're doing it from a command line you need to put the entire path in quotes.

Not:

C:\Program Files\My Stuff\Space Space Space\

But

"C:\Program Files\My Stuff\Space Space Space\"

I want to backup some files and directories but do not want to reset the directory modification or creation dates. Any suggestions? XXCopy would work but it doesn't use shadow volume services, so it can't really backup your whole system. Partition cloners would work but they only do partitions when I want a file copier. This should be easy, I mean how hard can it be to copy files!

More repliesFor some reason as I have added dates to cells, they are not getting converted to the Date type I have selected: 15/03/2009I type in my entries in this format:March 15, 2009 (usually it converts immediately but not this time)I've tried selecting the columns of cells with dates, Select>Format>Cells>Date and Type but nothing changed (although one date was converted to 03-15-2104Any suggestions?

Dat's cuz they're probably not being recognized as dates by Excel. My guess from afar is that the cells are formatted as Text and aren't accepting any other formats. Excel is finicky like that sometimes.Try this with one of your stubborn "dates"...let's assume it's in A1=DATEVALUE(A1)Now format that cell anyway you want.Then do a Copy...PasteSpecial...Values to eliminate the formula.

7 more repliesThe ?problem? I?m concerned about is that I noticed existingExcel 2007 xlsx files are now saving with fewer Kb.

I have a Lenovo W700ds and a W541. Both computers are running Windows 7 Pro 64bit.The W700 has Office 2007 and theW541 has Office 2013.Both versions ofExcel are 32 bit as far as I can tell.

Until recently I was primarily using the W700ds but have nowswitched to the W541.

after I saw a larger file losing KB when saved, I did a test with a file that had not been altered or openedfor over two years. I copied it twiceand added a -1 and a -2 to change the names.The file was a small 832 Kb file.When I opened and saved on the W541 running Windows 7 with Excel 2013,the file saves with 771 Kb.When Iopened the file using the W700 running Windows 7 with Excel 2007, the computeron which it had originally been created, the file saved as 830 Kb.Both saved smaller but the newer computerrunning Excel 2013 was much smaller.Iam seeing these same types of ratios of reduction in size for much larger Excelfiles as well.

I have not documented any actual problems or loss of data withany of the files to date and I have used an Ultra-Compare text file comparison which found no differences.

Is there any reason to be concerned about this? And, if so, what to do?

As with many people, some of my files are the result of manyyears of work and I don't want to do anything to compromise any of the data butI don?t know whether or not what I?m seeing is normal due to both Excel 2007and Exce... Read more

...I'm developing an issue tracker for my department and having trouble making my day tracker do what I want it to on the form. The datediff function is working fine calculating the days between the "Issue_Report_Date" and the current date, but I can't figure out how to stop it from running forever. I can change the criteria to only calculate through the "Resolution_Date" but then it ONLY displays a value if the ticket is resolved. What am I missing? Basically I want to track how many days a ticket is open, but need the counter to STOP and freeze once a "Resolution_Date" has been entered. Is there an expression I can use referring to null values or IF criteria?

In addition to the time issue, I also need to make some fields auto populate based on selected criteria. For example, when I select a POC for the ticket I want their phone and email address to auto populate on my form. POC, phone and email_1 and 2 are all in the same table.

Please HELP! I used to think I knew quite a bit about Access, but this is very frustrating.

Hoping someone can help me with this; I have a file which shows inventory levels submitted every Monday, along with sales between each week. I need to be able to create a query so that someone can enter a start and end date and they will be shown the difference between the opening and closing inventory for that period, along with the total sales for each week in that period.

I'm thinking it's probably fairly simple, but unfortunately so am I so I've not been able to work it out!

Any help much appreciated as I have a deadline of Wednesday for the report.

I've tried searching in this forum, but I either didn't find any, or I'm using the wrong keywords.

What I'm trying to do is get information from Table 2, with information from Table 1, but the criteria is that they if the (e.g.) names match, change the [address in Table 2] only if the [date in table 2] is more current than the [date in table 1]

As far as I can tell from the help files, they only provide comparisons between a given date #DD/MM/YYYY#, which means I can extract thru a query dates before, after, between a given date.

E.g. (Dashes just makes the table easier to read)

Table 1

Name Address Date

Aa-- 123---- 2008-01-01

Ab-- 234---- 2008-01-01

Table 2

Name Address Date

Aa-- 987---- 2008-02-01

Ab-- 876---- 2007-01-01

Query -->

Table 3

Name Address Date

Aa-- 123---- 2008-01-01

This Big database contains over 3/4 mil, and the dates range from 2002 to end of 2007. And I have about 7 smaller databases to compare and update with.

If anyone can point me in the right direction, it would be very much appreciated.

If a “Calendar Design” (a “Design Gallery” object) is inserted into a Publisher 2002/2003 document, one can click on it to display a small “Wizard” icon at the bottom of the calendar. If you click on that icon, a display similar to a task pane is displayed, with a “Change date range” button. If you then click on that button, a dialog box is displayed that allows the date range (for example, from October 2007 to November 2007) to be changed for the calendar template that is already inserted into the document.

I opened a Publisher 2002 document using Publisher 2007. When I click on the calendar, no wizard is displayed.

Here are the instructions in the Publisher 2007 Help file:

1. Open the calendar publication that you want to update.

2. In the Format Publication task pane, under Calendar Options, click Change Template.

3. In the Change Template dialog box, under Options, click Set Calendar Dates.

4. In the Set Calendar Dates dialog box, enter new dates in Start date and End date.

There are problems with those instructions that lead me to conclude that it is no longer possible to use the “Change date range” feature without completely replacing the calendar template. If there is someone who can confirm—or correct—that conclusion, please let me know.

Here are the problems with the Help file instructions:

1. I don’t have a “calendar publication.” I have a publication with a calendar inserted.

2. There is no “Calendar Options” choice in the Format Publicati... Read more

"I have searched the Microsoft Publisher online help. There is one posting asking the very same question, but no response."

There is one response now, and here it is:

"I haven't used the Design Gallery in 2007, but I did when I read [the] post. The Wizard Wand is missing, not only on the calendar, but on all the Design Gallery elements. This is a major bug and I will report it [emphasis added]. Sorry, I can't give you a workaround. You could use the regular calendar templates and change the date range on those.

--

Mary Sauer MSFT MVP

http://office.microsoft.com/

http://msauer.mvps.org/

news://msnews.microsoft.com"

There is no update available from Microsoft to fix this problem as of 10/12/2007

Filename: SysInfo.exe

Full Path: c:\Users\JIMBO\AppData\Local\Microsoft\Windows\INetCache\IE\RBAGCWGH\SysInfo.exe

____________________________

____________________________

Developers

Not Available

Version

1.0.0.2

Identified

6/27/2016 at 5:17:31 PM

Last Used

Not Available

Startup Item

No

____________________________

Many Users

Thousands of users in the Norton Community have used this file.

Mature

This file was released 4 years 8 months ago.

Good

Norton has given this file a good rating.

____________________________

Source File:

sysinfo.exe

____________________________

File Thumbprint - SHA:

8243b4ea661b060fe8cf4babc11ab5f51eadd28a0c9d66303183e8eceace8234

File Thumbprint - MD5:

Not available

Can anyone let me know the formula for coverting a date held in excel as yyyymmdd into dd/mm/yyyy.Many thanks

Assuming that is a text value try=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))where A1 holds the text date.

2 more repliesHi All,I am updating a old spreadsheet and wanted to add some work days down the side of it. The problem I have is that I only want Mon-Friday dates to be listed. For example....12/3/201212/4/201212/5/201212/6/201212/7/201212/10/2012etc.....For some reason I can't get it to work right. In this example you can see that the first work day for Dec is the 3rd and that the 8th and 9th are passed over because they are a weekend. I know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it. Anyone have any ideas?ThanksSpoiler

Quote from: Spoiler on December 14, 2012, 10:10:41 AMI know I have done this before I just can't remember how I did it. I was able to click on a cell and drag down to highlite a months worth of cells and put in a formaula in the first cell and then all the dates would just show up below it. Are dates for all rows part of the existing data? If so, then one approach might be to add a column to show the day of the week and then use a Filter to show all days except Saturday and Sunday.

6 more repliesI have been using this formula to let clients know when their bills are due:

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+90)

this ends up looking something like this: 1/1/04

however, I have had a request from them to have it look more like this: 1-Jan-2005.

Any help would be greatly appreciated. Thank you.

I've got a strange one, I think !!!Current date is 16 Jan 2005, and in my module I say Dim CurrDate As LongCurrDate = Format(Now(), "00000")This sets CurrDate as 38369Later in the module I want to compare this date with one from a cell ...Dim MyNewDate As LongMyNewDate = Range("N" & MyStoreRow) ... value is 17 Jan 2005The value being returned in MyNewDate is 38369, not, as I would expect, 38370 ...In another worksheet, the new date in Nxx is 18 Jan 2005, and this returns a value of 38370, not 38371 ... Any ideas as to what's happening here ?!?! Is my Current Date wrong ? Is this a known 'problem' ?!?!?

I'm not sure what is goung on but you would be better off working with Dates rather than LongsSub test()Dim mydate As Datemydate = Now()MsgBox mydateEnd Sub

6 more repliesI have a friend from california he has got office over here in europ but because of work he needs too change his dates in excel too american format how can i do this?

Eldest daughter driving me mad.At business she is working on a database in Excel.She tries to shorten the DOB entry as for example23/11/29 for someone born in 1929 but it comes out as 23/11/2029 yet when she enters 23/11/31 it comes out as she wants it as 23/11/1931. Is there something we need to format in Cells/Date?Brambles

and have the same problem! Using excell 2000 and my cells are formatted correctly. Very strange, however there are lots of very helpful people on this site and here's hoping.Taffy

3 more repliesI'm trying to set up a spreadsheet so that each time I open it, the time between two dates is calculated. The two dates would be today (the day I open the spreadsheet) and a date about 10 years away. Any help would be appreciated.ThanxDD

For examplein A1 30/03/2017in B1 =TODAY()in C1 =DATEDIF(B1,A1,"d")

10 more repliesI have two columns of dates, A and B. Column B is just As date plus 30 day. What i want it to do is, whenever i update column A's date, i dont want column B's date to change. how do i do that??

i dont want column B's date to change.Remove the formula that updates B?MIKEhttp://www.skeptic.com/

24 more repliesI have a sheet in excel which I input my daily results. Some dates or blocks of dates are missing as I am unable to gather the data. (vacation, travel, etc)Column A is the date (mm/dd/yyyy)Column B is the result (0.00)I have another sheet with the following:Column A has a list of all dates (month and day only) (366 rows) Columns B through M have the year in row 1I need to lookup the month and the day and attach the year based on the column year (this is easy)=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2)BUT I need the exact date data returned. VLOOKUP returns the closest lower value.If no exact date then return 0 or "".Is there an easy way to do this in Excel without writing code?Thanks for helping in advance!

If you want an exact match, you need to use FALSE or 0 in your VLOOKUP as the optional range_lookup argument:=VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0)Review the VLOOKUP Help file to see the options for this argument.If an exact match isn't found, this will return #N/A, so wrap the formula in an IF(ISNA()) function to get a "" of 0 if the VLOOKUP evaluates to #N/A. This will return "":=IF(ISNA(VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0)),"",VLOOKUP(DATEVALUE(MONTH($A2)&"/"&DAY($A2)&"/"&YEAR(C$1)),Generation!$B:$C,2,0))

4 more repliesI have a column of calendar dates (A1:A100) that do not include Saturdays or Sundays. I have certain production times for various products that I must base off of these dates and am having trouble counting an amount of days that doesn't include any Saturdays or Sundays in my count. Example below.

10-02-2006 I need to add 18 production days to this date. Workdays only being Mondays-Fridays, I need the answer of the formula to equal 10-25-2006.

Can anyone assist?

Ron

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!

thanks for all those who read!!!! let me know if anyone needs the code!

Hello

I have a date format on excel which increases the next date cell by 182,5 days and so on for the next 4 cells increasing it by the same amount... Does anyone know how to do this on Access 2007?

Dates input to Excel are being treated simply as a text string and not recognised as dates. Apparently, all was OK until recently (this is a friend's problem). Old spreadsheets suffer the same problem, so it seems to be a global problem.Any ideas please?

The cells have to be formatted to accept dates rather than text or numbers. Goto Format/cells after selecting those that need to be dates.

6 more repliesI am working with an Excel file and I have today's date autopopulating in cell C1 using the NOW function. In cell b12 I enter the date of an instance, for example 4/15/2013, then in C12 I am using the function of IF(B12="","",SUM(B12+30)) to give me 30 days from the date. In cell H12 I am trying to have that cell autopopulate if the date in C12 is older than today's date in C1. I have written the formula the way I feel it should work, but it is not working yet. Here is what I have =IF(C12="","",IF(C12<C2,"","X")). Once I get that working there is another formula for me to add where it turns the line red if there is an X in H12. It seems like a lot of stipulations and connections and I know if one thing is off, then more than likely even more will not work.Thank you

I don't see where you are using C2 for anything, so I can't answer any question related to IF(C12<C2,"","X").BTW, if you are only dealing with Dates, why not use =TODAY() instead of = NOW()?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more repliesIs there any way I can get a value produced that is the previous week day, ie not a weekend, that I can then export into notepad? To be run automatically everyday?

This should be simple and I thought I had it nailed, but the formula is not working, so I am obviously doing something wrong.I have two adjacent columns. The first column is always populated with a date and the second column may be populated with a date or may be blank. I am trying to determine if one or both dates are greater than 3/1/2008, then the row is "OK"; if neither date (or if the second column is blank) is before 3/1/2008, then that row is out. Here's what I tried to use: =IF(OR(H2>DATEVALUE("03/01/2008"),I2>DATEVALUE("03/01/2008")),"OK""OUT").The formula just does not return the correct result. Thanks for the help.

You are missing a comma between "OK" and "OUT"Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

17 more repliesNumber of working hours between two dates. Working hrs 6:00 AM to 8:00 PM. Including Saturday, excluding Sunday and Holidays

More repliesGood day i would like to ask how to make an if statement with datesit would appear like thisa1=any date depending on expected deliveryb1=date receivedc1=either "Delayed" or "Ontime"IF A1<=b1 then c1="Ontime" else c1="Delayed" Endsomething like this for excel..

Here is what you asked for:IF A1<=b1 then c1="Ontime" else c1="Delayed" EndThe syntax of an IF function is =IF(logical-test, value-if-true, value-if-false)=IF(A1<=B1,"Ontime","Delayed")However, I have a question.If A1 is the expected delivery date, and it is less than the date received, isn't the package Delayed?What am I missing?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more repliesI am trying to do the following:If the date is before 1/1/2014 in cell I15 I want cell J15 to say "yes". If the date is after 1/1/2014 in cell I15 I want cell J15 to say "no".Then, in another cell, I want if cell I15 is between 1/1/2014 and 1/1/2015 I want the value in G15 to be placed in cell K15.

For the J15 formula, what do you want to return if the date in I15 is equal to 1/1/2014?The same question holds for the formula in K15: What if the date equals one of the 2 dates that you are checking for?BTW, the solution will be based on the IF function. Have you read anything on the IF function in the Excel Help files or via a Goolge search? Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replieshow do i write a formula for this. if priority high add 2 days to date raised, if priority low add 14 days to date raised?

What do you mean by "date raised"?How will Excel know what is a High priority and what is Low?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more repliesI want to put hourly rates of $6 alongside weekdays and $7 for weekends.I did this it doesn't work:=IF(A2=weekday, 6, 7)Help?

The WEEKDAY function must refer to a cell that contains a date.Assuming your system is set up such that Sunday is weekday 1 and Saturday is weekday 7, this should work:=IF(AND(WEEKDAY(A2)<>1,WEEKDAY(A2)<>7),6,7)For any WEEKDAY not equal to 1 or 7, the function will return 6.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesProbably a simple solution to a fairly simple question but it's had me stumped for ages.In the first column i typed in a date (26-03-08)what i then want to do is drag this date down and for it to increase in value by weeks ie 02-04-08, 09-04-08 etc. Can it be done, or have i got to go through and type it all out (which would have been quicker now with the time i've spent mucking around).Many thanksNos

Providing the column is formatted as Date then enter the first two dates, select both cells and drag down.

2 more repliesHi there.

I am trying in Excel 2007, to do the following:

A guy phones in a fault. The date and time are recorded. When the fault is fixed, the date and time are recorded again. I need to calculate the response time, but I need it to only look at business hours, which are 8 to 5, Monday through Friday, when calculating this. Some faults may stretch over more than one working day, or over a weekend.

Is there a simple way to do this?

Thanks in advance for any help.

I think you'll have to Google for this

I can suggest the following link for starters:

http://www.cpearson.com/Excel/datetime.htm#WorkHours

I want to have two dates within the if statement. This is what I made, but i need it corrected. =IF(J2>DATEVALUE("5/31/2014"),"GOOD","EXPIRED" J2>DATEVALUE("6/31/2014"),"GOOD","EXPIRING SOON")

Fieldman,Sorry it's taken me a day to get back to you, I've been pretty busy...I don't know if I understand what you're wanting to do or not, but the only way that I'm aware of to put multiple "IF" statements into one field is to do it the way I demonstrated above. You can put as many as you'd like in there to achieve what you're trying to do.This is just a shot in the dark, but I've re-read your posts several times now and am wondering if it would work the way you want if you change the formula in A2 to something like this:=IF(A1>DATEVALUE("5/31/2014"),"GOOD",IF(A1>DATEVALUE("6/31/2014"),IF(A1>DATEVALUE("5/31/2014"),"EXPIRED","EXPIRING SOON"),"EXPIRING SOON"))I really think you will need to play with the formula above to make it work, but I think something like this is going to be your best bet.Law of Logical Argument: Anything is possible if you don't know what you're talking about.

10 more repliesHow do I write an If/Then statement in excel to change data to red if it's 6 months old? I have one field for the current date, B2, and a column of dates, B6-B40, that I want to know if the dates are older than 6 mo from today's date. If they are older then 6 months from today's date, I want the dates in column B and their corresponding data in column A to turn red. Any ideas? Thanks in advance.

See if this works for you:This is for Excel 20071) Select your cell or Range of Cells: B6-B402) On the ribbon click Conditional Formatting3) Click on New Rules, it?s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =DATEDIF(G3,TODAY(),"M")>6=DATEDIF(B6,TODAY(),"M")>66) Click on the Format button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKThis will highlight the cells that are GREATER THAN Six months.For Six Month OR GREATER use:=DATEDIF(G3,TODAY(),"M")>=6=DATEDIF(B6,TODAY(),"M")>=6EDITED, did change the cells from my test sheet.MIKEhttp://www.skeptic.com/

3 more repliesI would like to use the countif funcion to count how many times a product appears in a column by month. For example, I have two columns - Date & Product. The dates span over one year and I would like to know how many times a product appears each month.Hope you can help.Thanks, Mary

Assuming dates in column A and product names in column B:To count the number of product X sold in September (Month 9)=SUMPRODUCT((MONTH(A1:A10)=9)*(B1:B10="X"))

2 more repliesexcel insists on changing the year I enter (such as 2005) to the current year (2011) after I have specifically formatted the cells in the date format I selected. For example, I enter 5/2005 and end up with 5/2011. No matter what date format I select from the drop-down, it keeps doing this.

I formatted some cells as Custom m/yyyyWhen I enter 5/2005 it displays 5/2005 and shows 05/01/2005 in the fomula bar.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more repliesHi,Please help - I'm sure this has already been asked and answerred but I'm really struggling to find the answer I understand.In Excel - I'm trying to get a column of dates to add up and tell me how many date = Jan, how many = Feb and so on. Example. Dates column is formatted like 01-Jan-10. If there are 15 entries of Jan then I want the system to report back in another cell 15, if Feb had 24 enteries then 24 reports back and so on throughout the year.Thanks for any help! I'm sure I'm going to kick myself when I find out the answer - but thanks anyway!

HiCould you please let me know how can I add up the totals if when one the column contains dates? SUM( ( Sheet1!A2:A7 = "in" ) * ( Sheet1!B2:B7 ="Jan-10") * Sheet1!C2:C7 ) it return 0... ideally would be to get the total from c column if A column = in, and B column = Jan feb and march. is it possible?

B2:B7 ="Jan-10"Is it January 10th 2010 orJanuary 2010?MIKEhttp://www.skeptic.com/

9 more repliesI have crated a large excel spreadsheet where dates are in 3 columns ie day/month/yearIs there a way to get the dates in a single columnthanks in advancebbmf

Assuming that day, month and year are numbers in columns A, B and C=DATE(C1,B1,A1)

2 more repliesI have a strange problem I hope someone can shed some light on

I want to make a new column of months from a date

I have a detached with column A as a normal date format 01/12/2006 DD/MMM/YYYY

I then in column B use the month function - so = month(A1)

it displays 12 - and if I go onto the Fx to see the expression helper / wizard

I get told that i get a number returned 1-12 here 1 = Jan and 12 =dec

so that i format the =month(a1) into a MMM-YY format and

get Jan-1900 - I'm cool with the 1900 as i have not specified YYYY but why do i get Jan returned instead of Dec

But what I'm after and maybe a simpler way is to have a dropdown on my pivot table of MM-YYYY from my date field rather than DD-MMM-YYYY

so I can choose and show month data

I'm sure I did this in the past just by using format - MMM-YYYY on a date field and it worked in the picot table OK

any help appreciated

Number of working hours between two dates. Working hrs 6:00 AM to 8:00 PM. Including Saturday, excluding Sunday and Holidays

More repliesIn an excel spreadsheet, I have two colums, each with a date in it. I want the third column to yield the greater of these two dates. For example, column one has 11/10/09, column two has 12/15/10, I want column three to show 12/15/10 as the greater of these two dates. Thanks.

Try this:If 11/10/09 is in cell A1andIf 12/15/10 is in cell B1in cell C1 enter the formula: =IF(A1>B1,A1,B1)MIKEhttp://www.skeptic.com/

4 more repliesHi,I am looking to write If statement that if I write a date into cell A2, then A1 will display "Good" if its within 4 years since the date in A2, or A1 will display "Expiring Soon" if its between 4-5 years after A2, or A1 will display "Expired" if its 5 years after the date in A2. I have never used If statements with dates before so I don't know where to start and any help will be greatly appreciated.Thanks,

re: "if its within 4 years since the date in A2"By its I assume you mean today's date. If so...Take a look at the EDATE function. EDATE returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date).e.g. For your 5 year (60 months) expiration criteria, this should work.=IF(EDATE(A2,60)<TODAY(),"Expired","")Basically what this says is "If I add 60 months to the date in A2 and the result is less than today's date, then the date in A2 must be more than 5 years ago."The "inverse" of that is to subtract 60 months from today's date to get the same result:=IF(EDATE(TODAY(),-60)>A2,"Expired","")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

3 more repliesHi there

I'm having problems with dates in a macro. To simplify things, here is an example of my problem. I have code that goes something like this:

Range("K1").Select

Do

If Range("k1") <= Range("D9") then

ActiveCell.Offset(0, -1).Range("A1") .select

End If

Loop Until ActiveCell >=Range("K1") or ActiveCell = ActiveCell.Text

Cells K1 and Cells D9, C9 and B9 all have dates. Assume that the dates are as follows:

K1 = Sept 05, 2005

D9 = Oct 15, 2005

C9 = Sept 05, 2005

B9 = Aug 2, 2005

A9 = text

The problem is that the cursor stops at the text cell (A9) and completely by-passes all the dates. It should stop at Cell C9. If C9 did not equal K1, then the cursor should stop at cell D9.

The macro works fine if I take out the inequality signs and just leave the equal signs. What Am I doing wrong??

Please help.

Mario

Hello,

What a great help this forum is.

I know nothing at all about VBA, yet managed to get a macro in excel that allows me to send the "due date" emails that works almost perfectly for my needs. I based my macro on the code found here https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/

But i'm still missing one function in this macro for it to be perfect for my needs. I want it to send only one email to the "area manager" specifying all the items (serial number and location) that follow my due-date rule (instead of one email per item). I want it to send a list of items that are due this month for example.

Is it possible?

Working with Excel 2000 is there a way to filter dates?

Thanks for your help.

I am trying to subtract two dates in excel but I keep getting weird results...

My dates are formatted like this: 8/11/2010

I am using TODAY() to return the current date. Then I want to compare that to see how many days have passed since a given date. So my formula is this: =TODAY()-D2

Now, it seems to kind of work because the result I get is 1/30/1911. 30 days is the correct answer! But why am I getting the full date?? How can I fix this? And why the heck is it saying 1911 - on another computer I tried it says 1/30/1900...

??

Look into the Datediff function. I think for what you want (doing this from the top of my head) is Datediff("d", D2, TODAY()) or you can sub TODAY() for NOW(). Like I said it is from the top of my head but should be something like that.

This is multi-part question on how to create different formulas on the attached worksheet.

#1 - I would like to count the number of Start Dates IF the Start Date is blank or greater than today, but only IF there is a date in the Requisition Date column. This would be to indicate the total number of open positions as of today.

#2 - I would like to count the number of cells in column A that are greater than or equal to 17 IF the Start Date is blank or greater than today, but only IF there is a date in the Requisition Date column. This would indicate total number of open positions that have a grade lever of 17 or above.

I have two columns of dates - a Due Date and a Completed Date - I want to compare the dates in each column and have the results appear in a table I have created on a separate sheet in the same workbook. If the completed date is after the due date for a certain type of information (contained in another column; ie, IMAC or Out of Scope), I want to count that instance as "Missed" in my table. Any help would be greatly appreciated!

I don't quite understand how the "IMAC or Out of Scope" information relates to your question.If all you are trying to do is compare 2 values (dates) a simple IF function should do it for you:=IF(A1>B1, "Missed", "")This will return Missed when A1 is greater than B1 and return a blank cell if not.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more repliesHello,I am trying to do the following in Excel:If the date in column Q1, is 30 days over the date in column P1, outline or fill Q1 in a color. Is this an IF statement or conditional formatting?Thank you!

Conditional formatting is what your looking for:1) Select your cell, Q12) On the ribbon click Conditional Formatting3) Click on New Rules, it?s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =IF(P1+30<=Q1,TRUE,FALSE)6) Click on the Format button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKMIKEhttp://www.skeptic.com/

2 more repliesForgotten how to format dates in excel so they accumulate down the column.e.g. has to be like this down the column1st January 1950, 1st February 1951, 1st March 1950 .... 1st January 2006 help much appreciated. Durko

Should it be 1st March 1952? If so:Tools > Add-ins, make sure Analysis Toolpack is ticked.With the first date in A1 enter in A2=EDATE(A1,13)and copy down as far as needed. Select Column A, Format > Cells and set to display as you wish.

4 more repliesQuick question.

Lets say I have a date.

11/01

Is there a formula that would return and IF statement?

For example

I am looking to all the dates between 12/15 and 01/15

I have tried to create a formula using the IF statement, but cant only get the first half.

=IF(A1<=12/15,"TRUE","FALSE")

But how do I get the second half for it to check if if the date is less then 01/15? every time I try I get an error.

Howdy. If I understand correctly. Be sure to check year, since the year will be in the cell whether it displays or not.

=IF(AND(A1<=12/15,A1>01/15),"TRUE","FALSE")

I would like to build a spread sheet so when I enter a date the adjacent cell will have a date according to some rules. For example

Dates entered between Jan-01-2013 and Mar-31-2013 the adjacent cell will have a date of July-01-xxxx (where xxxx is the same year as the entered date. Can someone help me out with this.

thanx

Hi,

Need Urgent help. Need a formula, explained in detail in attachment and trying to explain here

Column A (Inv Value) Column B (Col.Date) week1 (14/4/2012 - 18/4/2012)

4500 1/5/2012 0 (formula)

225000 23/4/2012 0

55393 17/4/2012 55393 (formula)

* Column A and B are Data field from where we will take data.

* Column C and D are the fields where i need to put formula

**** If column C date range is in Column B date, then put the value of Column A

I can split Column c in two cells with start date and end date if needed (then merge remaining cells)

Appreciate your help and support on urgent bases to solve my issue. < removed email address - etaf moderator >

Hey there,I would appreciate if i get help on Excel Date formatting. Problem Statement: when I download the report from Business Intelligence, I get certain columns with Dates. The cell values are so rigid that, I can't format the dates which are in System driven format, and few are easy to convert to MDY, or DMY or whatever. But few, I cannot change. Second Problem: When all the data is related to Quarter 1, when I try to format the date, I end-up getting dates as 02-Sep-2011, but the actual date is 02/09/2011 when I pull it from reporting system.The date should be 09-Feb-2011. I have some 3000 lines of such adamant dates, help me to get out of this issue.Positively seeking anybody's help.Gracias/Thanks/Saludos/Dhanyawad/Shukriya

re: "I can't format the dates which are in System driven format,"I don't know what you mean by "System driven format" but I can tell you that very often data that gets downloaded from websites and/or other applications ends up in Excel as Text instead of numbers or dates.Since I can't see your spreadsheet from where I'm sitting, I'm going to take a guess and suggest that both of your problems are related to each other.Let's start with Problem 2:re: I end-up getting dates as 02-Sep-2011, but the actual date is 02/09/2011 This statement does not really make sense. 02/09/2011 could actually be 02-Sep-2011 or it could actually be 09-Feb-2011, depending on how Excel (actually Windows) is set up. Let me explain.The format in which Excel recognizes dates is based on the settings in the Regional and Language Control Panel, Customize button, Date tab. This is what determines whether the first 2 digits represent the day or the month.My guess is that the values that you cannot format as dates are those where Excel can not recognize the month.For example, since your system is recognizing 02/09/2011 as 02-Sep-2011 I'll assume that your Windows setting is something like dd-mmm-yyyy.Excel has no problem changing 02/09/2011 to 02-Sep-2011 since 09 is a valid month, but it wouldn't know what to do with 02/15/2011 since there is no 15th month. My guess is that Excel decides that values like 02/15/2011 must be text and therefore cannot be formatted as a date.Try changing your Control panel s... Read more

3 more repliesWhen cells are formatted as date in Excel 2000, they change to a 5-digit number (starting with 3). Has anybody come accross this, and does anybody know a way around?

Try using =DATEDIF()=DATEDIF(1stDate,2ndDate,"Interval")1st Date : Earliest date. 2nd Date : Most recent date. "Interval" : What you want calculated. Must be surrounded with quote marks. Intervals can be: "d" Days between dates."m" Months between dates."y" Years between dates."yd" Days between the dates, as if the dates were in the same year."ym" Months between the dates, as if the dates were in the same year."md" Days between the two dates, as if the dates were in the same month and year.MIKEhttp://www.skeptic.com/

6 more repliesDoes Excel (version 9.0) have a feature which will allow me to compute various day/month/year time lapses? For instance can I compute the number of months between Oct. 1, 2003 and September 31, 2013? Or if I want to determine what day it is 25 years from today? I can do this on my handheld, but would like to design an Excel formula for it. Any help would be appreciated.

Use the datedif function

i.e. =DATEDIF(Date1,Date2,"Interval")

Where

Date1 is Start Date

Date2 is the end date

Interval is one of the following: You must use the inverted commas

"m" Months

The number of complete months between Date1 and Date2.

"d" Days

The number of days between Date1 and Date2.

"y" Years The number of complete years between Date1 and Date2.

"ym" Months Excluding Year

The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.

"yd" Days Excluding Years

The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.

"md" Days Excluding Months And Years

The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year.

Some times I have only the year (eg 1914). Other times I have the full date (eg 12/25/2011). If I format the Excel for as a date and enter merely 1914, it shows as 3/28/1905 (its reading only the "1914"). I do NOT want to enter 1/1/1914 - because that is an incorrect date (all I have is the year 1914). What I want: If I have only the year, I want only the year (eg 1914) to show. If I have the full date, I want the full date (eg 12/25/2011) to show. How do I do it? Does it require an IF statement?

Not sure what it is your after, but Excel stores dates and times as a number, representing the number of days since January 1, 1900So when you enter 1914, Excel reads it as 1,914 days since 01/01/1900or 03/28/1905.See this page for a complete explanation of how Excel works with Dates/Times:http://www.cpearson.com/excel/datet...MIKEhttp://www.skeptic.com/

5 more repliesHi All,

I have spent all day trying to figure out how to do this and when i think i have done it.. something is missing!!

So, when the date in column A = Today, i need an email to be sent to the email address in column F stating the below:

"Hi All,

Engineer (Column B), is visiting Customer (Column C) today at (Column D) to complete (Column E)

Many Thanks"

I just cant get it to work!!

Please can someone advise what i'm doing wrong?

Sub eMail()

Dim lRow As Integer

Dim i As Integer

Dim toDate As Date

Dim toList As String

Dim eSubject As String

Dim eBody As String

With OutLook.Application

.ScreenUpdating = False

.EnableEvents = False

.DisplayAlerts = False

If (Cells(i, 1) = TODAY < "") Then

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

toList = Cells(i, 7)

eSubject = "Engineer " & Cells(i, 2) & "job list " & Cells(i, 1)

eBody = "Hello All, " & vbCrLf & vbCrLf & "Engineer" & Cells(i, 2) & "Is at customer" & Cells(i, 3) & "in" & Cells(i, 4) & "and is" & Cells(i, 5) & vbCrLf & vbCrLf & "Many Thanks"

On Error Resume Next

With OutMail

.To = toList

.CC = ""

.BCC = ""

.Subject = eSubject

.Body = eBody

.bodyformat = 1

.Send

End With

On Error GoTo 0

Set OutMail = Nothing

Set OutApp = Nothing

Cells(i, 5) = "Mail Sent " & Date + ... Read more

Hey

I have imported data from an outside database and it brought in the dates seperated. The information is in columns and the day, month (which is in text), and year each have their own cell. There are hundreds of dates. Due to space considerations I need each date to only take one cell. Is there any way to do this without having to do it by hand?

Not sure which version of Excel you are using, but am assuming 2007. I will also mention what to do if you are using 2003 or earlier

Create a blank worksheet and do the following so that you can see how the following formula works and then how to convert it as a value rather than a formula

In A1 type 24

In B1 type June

In C1 type 1990

In D1 type =DATEVALUE(CONCATENATE(A1,B1,C1))

Assuming you have similar data in the Columns A, B and C, copy the formula in D1 down column D until you have a formula for each row of data in A, B, C

Select all the formulas in column D and click on Home Ribbon tab ans click on the Copy Icon to place it in the clipboard. 2003 and earlier Edit, Copy

Whilst those cells are still selected click on Home ribbon tab and click on the little down arrow just under the Paste button and select paste values. 2003 and earlier Edit, paste Special, Values OK

Your data in column D will now be as a date (not a formula) which in the background is treated as a number, which then allows you to do calculations on the dates.

Now select columns A, B and C and delete those columns

You are now left with column A and the dates in single cells.

Hope that helps

Hello to all,

recently my excel started to change the dates lets say from 02/01/08 to 06/08/02 etc. But it doesnt change all dates just some. Please, help me with this.

My operating system is Windows XP.

Thanks,

I have an excel document. How can I get excel to automatically populate several fields based on a date in another field. For example: If field A5 has a date of 02/01/2011..how do I have Excel automatically generate fields C6 thru C14 with dates of 2/1 thru 2/8? The fields in C6 thru C14 would change based on the date entered in C5. Any help is greatly appreciated

In C6 enter this:=A5In C7, enter this and drag it down as far as you need:=C6+1Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

9 more repliesI want to have an IF true/false statement stating that for a column of DATES, if the date is before "5/1/2013" TRUE and everything else false within the if statement. thank you in advance!

Try this:With your column of DATES in column A,put this formula in cell B1 and drag down.=IF(A1<DATE(2013,5,1),"True","False")What happens if the date is exactly 5/1/2013?See how that works for you.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

2 more repliesI am having trouble figuring out how to write the correct function using countif. I have two columns. One column has the start date and the other column has the end date. I want a separate cell to countif those two dates are between the specified dates.

start date end date

jane doe 1/22/2008 4/3/2008

February ?

So for February I need to countif those two dates fall between 2/1/2008 and 2/28/2008.

Please help.

I am having problems working on a spreadsheet of dates and am looking for help. I am creating a file to show membership ages and senority in our area. Here is my setup.The first column: "Members name". Second column: "Date of birth". Third column: DOB converted to automatically updated "Age" by Year and Month with "Datedif". Fourth column: "Hire date". Fifth column: Hire date automatically converted to "Length of Senority" by Year and Month with "datedif".I want to get the totals and averages of the Age and Senority columns. I think I have the averages worked out by using =AVERAGE(B2:B49) and then converting that with =DATEDIF(B53,TODAY(),"Y")&" Yr, "&DATEDIF(B53,TODAY(),"YM")&" M". But I am not having any luck with the totals.Can anyone help?

It is rather difficult to visualise this.Could you upload the file (with the members names removed) to a site like click here and provide a link to download it.

6 more repliesI'm building a project database. Each project has an "approval date" in the format of mm/dd/yyyy. I'd like to let the user enter a year and then find all the projects (records) in the table that were approved in that year. The textbox they enter the year is named dateApproval, the table is tblProjects, and the column in which the approval date is recorded is BoardApprovalDate. Any suggestions on how I should write this VBA? Here's what I have so far:

Code:

If Not IsNull(Me.dateApproval) Then

SQL = "SELECT tblProjects.BoardApprovalDate " & _

"FROM tblProjects " & _

"WHERE BoardApprovalDate = " & Year(Me.dateApproval) [COLOR=DarkRed]'comparing different formats...[/COLOR]

Set rs = CurrentDb.OpenRecordset(SQL) [COLOR=DarkRed]'you can probably stop reading here[/COLOR]

rs.MoveLast

rs.MoveFirst

If rs.RecordCount > 0 Then

For count = 1 To rs.RecordCount

strWhere = strWhere & "([ProjectID] = " & rs![__pkProjectID] & ")Or"

rs.MoveNext

Next count[INDENT] End If

[/INDENT]End If

And am I even using that Year() function correctly? Ideally, I'd like to wrap the BoardApprovalDate in the Year() function, but I don't think I can wrap a whole column in that function.

I need to figure out how to create a query that groups data by date. But the tricky part is that I need to group by Year, not the actual dates recorded in the columns. To make it even more tricky, it needs to be a running total.

Eventually, this query is going to be used as the source of a line graph that shows how many projects our company has "approved" over time. The X axis is going to be Year and the Y axis is going to be "Number of Projects"

Each project has its own record in the database, along with its own date on which it was approved in a MM/DD/YYYY format.

As for the running total: the graph is going to be constantly going up. For example: if in 2000 we had 0 projects approved, then in 2001 we approved 5 projects, then 2000 would have a point at 5. Then in 2002 we approved 2 projects, the line would need to go up to 7, not jump down to 2 for 2002.

How can I get my query to return the Year on the X axis and the running total on the Y axis? Is this even possible, or can I only get it to work in Excel?

Attached is a picture of what the end chart is going to look like (in Excel... but I need to use Access).

Thanks.

Hi everyone! I support a medium-sized law firm and we've recently upgraded from office 2003 to 2007. Needless to say there's been quite a number of small glitches that we've been trying to work out for a while and they've been getting me stumped.

One problem is that a number of users use tasks to handle deadlines and they're frequently updating the due dates several tasks at a time. Some have been reporting that if they update about 20 due dates they can no longer update any more dates until they restart outlook. One guy also said that after he sent an email the due dates were updating again, but pushing the send/receive button didn't cause the same effect, which seems confusing to me.

It seems to me that it might be an issue with the exchange server and possibly caching, but I can't figure it out.

We're using exchange 2003 SP1 for our mail by the way. All help is greatly appreciated, thanks!

windows nt 6.1 vlookupI've created a spreadsheet in Excel. I've also created a calendar on a separate sheet. I need for the dates on the calandar to populate onto the spreadsheet. I know the VLookup formula. However, it's not populating onto my spreadsheet.I'm using Windows NT 6.1.

Let's start with the fact that your OS (Windows NT 6.1) has nothing to do with this. VLOOKUP is an Excel function, unrelated to the OS version.Next, you said:I know the VLookup formula.You might, but we don't, nor do we know how either of your spreadsheets are laid out. Since we can't see your workbook from where we're sitting, there is no way for us to offer any suggestions related to your issue.Perhaps if you posted some example data, along with the formula you are using and the expected output of that formula, we could be of some assistance.Before posting any example data, please click on the following line and read the instructions found via that link.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesI've created a spreadsheet in Excel and the dates are formatted as for example 07-Nov-05.This works when entered directly into the spreadsheet as 07/11/05.I have also created a form in vba and when the date is inserted as 07/11/05 it enters the spreadsheet in the form of 11-July-05. Those Americans get everywhereThe obvious question is: What have I done wrong/omitted to do, and how can I correct or amend it.Hope you can help.

You've done nothing wrong.Excel only has American style date formats

7 more repliesHello.

I am exporting different data (about the same records) from a couple of sources into Excel.

I then use VLOOKUPs to collate the data into 'complete' records.

One particular thing I need to do is flag late records as "Late". I tried to do this by entering a formula similar to this:

=IF(H2>B2, "Late", "")

Where H2 is the Delivery date and B2 is the Required By date.

These two dates are from different sources. The problem I have is that the Required By dates are not being recognised as dates.

The Delivery dates are in the UK format dd/mm/yyyy and are fine.

The Required By dates appear to be in the format dd/mm/yy.

The IF calculation fails to yield any results.

You know when you change a date format to a number format, it shows the 5 figure date reference number? Well, that happens with the Delivery dates and not with the Required By dates. In other words, Excel is failing to recognise the Required By dates as dates.

The only way around this I've found so far is to Find & Replace on each date - so I'll find all instances of 01/07/04 and Replace with 01/07/2004. Then I'll do the same with each subsequent date in the period (usually about 2 weeks worth of dates).

Is there any way I can force Excel to recognise these dates, or to convert them to dates in a quicker way?

Thanks

Gram

Hi Gram ; what does the "Required By" format show up as initially -- text?

Try entering "1" (the number) in a (General format) free cell & copying it. Then select the range of "Required By"s & Paste Special -- Multiply. This may well convert them to serial #s.

HTH,

Andy

Hello - I've been spending most of the day trying to figure out a solution and seem to get further and further away. I have 2 columns. The first column contains a date for delivery (P5) the second column shows a forecased date for delivery (Q5). I need to create a formula that will change the color of the Column Q cell for the following: if it is equal to or less than column P it will be green. If 1-14 days past column P it will be yellow and more than 2 weeks past the column P date, the cell will be red.I hope I explained that well, but if anyone can help it would be much appreciated!

See if this How-To helps:http://www.computing.net/howtos/sho...MIKEhttp://www.skeptic.com/

2 more repliesI'm attempting to use the VLOOKUP function to look up dates, which I've successfully done before. This time around, VLOOKUP does not seem to be finding the dates I'm looking for, even though they exist and are an exact match. So here's a sample.

So my dates look like this:

Date/Time

9/1/2007 13:00

9/1/2007 13:27

9/1/2007 13:58

9/1/2007 14:00

9/1/2007 14:35

9/1/2007 15:07

9/1/2007 15:48

9/1/2007 16:00

Now I only want the values that have been reported on the hour, so my lookup values look like this:

9/1/2007 13:00

9/1/2007 14:00

9/1/2007 15:00

9/1/2007 16:00

My function looks like this:

VLOOKUP(B2,Sheet1!$B$1:$F$12422,3,FALSE)

Excel seems to be finding some dates successfully, but other dates it returns a #N/A. I have used an if statement to test some of the dates that are not being successfully found. So I use the function IF(A1=B1,"YES","NO"), where A1 is the date in the date in the table, and B1 is the matching date I am trying to find...and when I do this, I receive "YES", which to me suggests the dates are exactly the same, formatting and all, so Excel should have no trouble finding the exact match. I have also tried converting the dates to the serial number (i.e. 12/13/2006 20:00 = 39064.83333), and although the numbers match exactly, that has not solved the problem either.

Can anyone help me out?