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?

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

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 repliesHi all,

I want to generate dates automatically in Access 97 for a contact database. For instance, if the last contact was made on day a, I want to generate in the "next call" column day a + 3 months/90 days (call that day b). If a contact was made before day b, I want the column to generate a new next contact date equal to b + 3 months/90 days. (As for the 3 months/90 days, I can go with either).

Finally, I would like the system to send an e-mail notice, if possible, telling the person here at work that person x is to be contacted on this date. (We don't use Outlook, or I suspect some of this could be done using that).

Thanks for any advice in advance.

Use DateDiff function to determine the number of days compared to the current date. If it is >= 90 you can call an email function or module.

I know variations of what I want to do exist but I have not had success in implementation. I am using Excel 2007 and MS Outlook.

After opening the file, I would like the macro to look at all the dates in the sheet and send an email (.Display is preferred) each time a date gets within 1 year, 6 months (180 days) and 90 days of the current date.

The headers in the columns are:

A-First Name

B-Last Name

C-email address

D, E, F, G, H, I, J-Certification 1, 2 ,3 etc. (All contain various expiration dates)

K-1 Year email sent. I've gathered from reading that another column indicating if an email was sent is a good idea and may be needed to trigger the other events.

L-180 Day Email Sent

M-90 Day email sent

I have 2 excel files. I need them linked because in file 1 I have different activites that are on our books for a specific date. I want those activities, based on today's date, to automatically come over to file 2 so that when I open file 2 everday, the proper activites come over to that file. Any suggestions? thoughts?

Hi,

If I have a column A with dollars in it, and column B with dates,

how could I write a formula to give me the total of column A

only when the date in B falls between 8/1/02 and 8/15/02?

I can get it to work if I say "<8/16/02" or ">7/31/02", but I can't get the range syntax....

Thank you!

kimmer

Mr. Database created the macro in this spreadsheet and it works great. I need to know how to update the macro when I need to add rows or columns.

HI

I am looking at a partial automation requirement on Excel and Outlook 2016.

I have an database of my team in excel.

their date of birth is on b2, (EG : 23rd March 1973)

their email id is on h2

today being : 23rd March 2016, i would like an automated mail to go to their id and the rest of the organisation with a Happy Birthday Message and a random Birthday Pic.

The better if the Birthday message can be different for different colleagues.

any help from any of you will be credited on the sheet with your name.

I would appreciate help with an excel formula. I am not a techie so I would appreciate if responses are as transparent as possible. Column J lists participant start dates and Column K lists participant end dates. There are a myriad of start and end dates and many participants so the sort function does not suffice. I am hoping that it is possible to write a formula in which I can check total participants onsite on any given Monday (or week) of the year. Any help would be great. Thanks very much.

Hello,I am trying to create a column on my excel tracker that will give me the last date due before something is late. For example, I have Report A which is due to me 30 days before my suspense date, but Report B is due to me 60 days prior. Is there a way to make a formula that if Column A has the suspense date and Column B has the report type, Column C will calculate the date due based off the above conditions? Thanks in advance for any help.

=IF(B1="Report A",A1-30,A1-60)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesHi All.

Hi i have a excel file which has Serial No , Task and date of completion as 3 ccolumns.

I keep on missing the tasks. I heared from somwwhere that we can create automatic reminder in outlook based date picked from excel. I want the same. Example on 1, april, 2010 - if i have to go to my lawer, the in outlook on 1st-Apr-2010 a reminder comes , visit to lawer Mr. XXXXXX.

If any one can help that would be of great help.

Regards,

sam

Hi, I am really new to excel so I am hoping to get some help here. I have searched through the forum and tried several codes, but I was not able to run it successfully.

Basically in my excel file, i have a long list of documents with their respective expiry dates. I want excel to send me an email when it is 6 months before the expiry date.

I have uploaded a sample file on what my worksheet will look like.

I am using Microsoft Excel 2010.

Thanks!

I manage a small database of paid memberships for a non profit. We are keeping up with membership dues and renewal dates in an excel spreadsheet. I would like to automatically generate an email through outlook 30 days before the renewal date, again 14 days before the renewal data (if they haven't renewed), again 5 days before renewal date (if they haven't renewed), and again 5 days after it has expired. Furthermore, I would like to customize the message of the emails if possible, including my signature that is saved in outlook.

In my limited knowledge of VBA/Macros, I would assume that I will need a column that is checked if a membership has been renewed, so that the macro will know whether to generate an email. Also, I am assuming that this would be easier if I had multiple macros instead of just one.

I am attaching a excel sheet that has the columns we will be using with dummy data.

Thank you.

Hi Everyone

I'm needing to automate a process that notifies a group of users to complete specific data on a shared Excel template file. The process is date driven. For example; row 1 has data due on 4/25/2017 by Bob Smith, while row 2 has similar data due on 4/29/2017 by Rose Hill, etc. We are in a Windows 7 environment, Outlook 365 mail clients and recently updated to Office 2016 from 2010.

Anyone have any ideas on how to best approach a solution for via Excel macros?

Our employees can sell such things as vacation time, for example, 1 day of vacation pay but their request must be submitted by a certain time if they are to be paid. We can tell them what day they will be paid based on the date they submit their request. This can be confusing for a supervisor to explain and I wanted to automate it on an Excel sheet.

Example:

1. Employees receive their regular check every Friday as well as a check for any time sold.

2. As long as they get it into us within 7 days prior to Friday they will receive their check the next Friday.

3. If it’s greater than 7 days then it would be the Friday after next before they received the sold time check.

4. For example If an employee requested on May, 12th 13th, 14th, 15th, 16th, or 17th to sell vacation time that check would be received by the next Friday i.e. May 25th. However, if they turned it in on May 18th. 19th. 20th, 21st, 22nd., 23nd, or 24th. they would receive it on June 1st.

5. This pattern continues throughout the year. Note that weekends are included.

6. I think the attached will better explain what I am trying to do.

I think there is something wrong in the way you explain it but, could be my interpretation though

I adden a column using the Days360() function which shows the nr of days between the request date and the 'friday' date, maybe you can use that resulty to compute the new value.

I work in construction. We'd like to know if there is a way to work a calendar backwards. We have a "due on site" date, a material lead time and need to figure out when the item needs to be approved by in order to meet the "due on site" date.

Please help, thank you for your time.

Katy

Hi there, welcome to the board!

Yeah, you can absolutely do that. Dates are serial numbers in Excel, so you can do math on them. The question really becomes where is your "due on site" date located, and how do you want to calculate the approval date? Is this just a set number of days prior? Should it skip weekends? Holidays?

For a straight day calc, just mins the values:

Code:

=A2-B2

'assumes "due on site" is in A2

'assumes material lead time (in days) is in B2

For skipping weekends and holidays we can make use of some of the many date functions in Excel, like WORKDAY() or NETWORKDAYS(). If you specify what you start with, and the expected results, we can write the function(s) for you.

Tech Support Guy System Info Utility version 1.0.0.2

OS Version: Microsoft Windows 7 Enterprise, Service Pack 1, 32 bit

Processor: Intel(R) Core(TM)2 Duo CPU E7500 @ 2.93GHz, x64 Family 6 Model 23 Stepping 10

Processor Count: 2

RAM: 3037 Mb

Graphics Card: Intel(R) G41 Express Chipset, 1294 Mb

Hard Drives: C: Total - 305242 MB, Free - 67280 MB;

Motherboard: LENOVO, To be filled by O.E.M.

Antivirus: System Center Endpoint Protection, Updated and Enabled

hello

i am new here i might stumble a bit, my apologies in advance. i am a Marine engineer by profession and now i have been helping out in the office since we don't have a Technical Manager, my computer software skills are also limited that is why i need help.

i have 45 crew reporting to me with all sorts of certificates with different expiry dates. i have managed to compile an excel spreadsheet with their names including certificates but since i am in between operations and the office i need excel reminders to my emails and to one nominated person at least 60 days before expiry to avoid catastrophic shipping delays while people are revalidating their certificates.

please find attached spreadsheet with names and relevant certificates (highlighted in green). your assistance will be forever appreciated. i will be glad if i can able to do it by myself so when changes are needed i can just execute them. thank you in advance.

I would not be able to code this, but a coder would have needs/questions:

1. A worksheet with Name and Email address. This is so that info does not need to be hard-written into the code.

2. When do you want it emailed? When you open the file? Automatically, even if you don't open the file? With a button click by you?

3. What version of Excel and Outlook are you using?

Hi – I need some help.

I have a log that lists the start date and end date of my fleet of cars. I want to be able to tell how many cars we had in our fleet on any day (and then be able to average out the number of vehicles in a month)

What I have is two columns with the first containing the start date of the car and the second containing the termination date of the car.

Example

Code:

21/04/2006 20/04/2010

21/04/2006 20/04/2010

27/04/2006 26/04/2010

28/04/2006 27/04/2010

1/05/2006 28/04/2010

3/05/2006 29/04/2010

3/05/2006 30/04/2010

12/05/2006 11/05/2010

17/05/2006 1/05/2010

On another sheet I have dates starting 1/1/2003 incrementing by a day up to 2013. What I am looking for is a way to count how many times each of the dates falls between (or on) the date in each of the ranges.

EG in the example data above 5/5/2006 = 7, 6/5/2006 = 7, ..., 12/5/2006 = 8. (I hope! )

Note - I am using dates in the format DD/MM/YYYY

Hope you can help!

Once again I'm reaching out to the TSG forums for help. I have a column (bid request date) and another column (bid approved date). In my pivot table I have a count of these by my team members. # of Bids Submitted and # of Bids Approved and I want to add another column into the pivot table, "Approval rate" which divides the # of Approved by # of submitted. Is there an easy way to do this in a pivot table?

Hello,

My new problem is this:

I have calendar dates entered into a table that has records of transactions. In the reporting I need to create, the business months are different than the calendar months. For example, for July in the reports, I should do a summary based on the business month, July 5 to Aug 1.

My idea is to create another table where I would enter the start and end date for all business months, plus two other fields for the correspoding business year and business month (Business_Year, Business_Month, From_Date, To_Date; From and To are calendar dates in date format, Business Year and Business Month are numeric).

What I need is a query that would look at every record in the transaction table, then would look that calendar date up in the business months table, looking up which business year and business month it falls into, and return the value of the business month and business year for every record in the transaction table.

I was thinking about using DLookUp() function in the query that is based on the transaction table.

The best I could find on the net is something like this:

Year: IIf([Date]>[Business_Months].[From_Date] And [Date]<=[Business_Months].[To_Date],Year([Date]),"9999")

Month: DLookUp("Business_Month","Business_Months","Date > #" & [Business_Months]![From_Date] & "#") & DLookUp("Business_Month","Business_Months","Date <= #" & [Business_Months... Read more

Hi there I tried linking my serial number and product number of my laptops with my hp passport, but that does not seem to to work. The warranty checker start date november 2014 but we bought them in october 2015, so we are a year out. How do I update these, and am I able to link them to this hp passport account. Thanks for your time. All the best Matt

Hi melton , In this to get you warranty to be update please do contact the HP support line of your country with the Proof of purchase with the serial number on it . So that they would work on it and get it updated . HP Worldwide Telephone Numbers http://h10032.www1.hp.com/ctg/Manual/c00047049.pdf Also as you have the the passport account you can raise a request throught the support case management . r-K //Click on Kudos and Accept as Solution if my reply was helpful and answered your question// I am an HP employee!!

1 more repliesI am looking to create a macro that when run goes through a sheet and adds all cells that are in the same row as a certain date and places them in a cell on another sheet. I want to macro to cycle through all the days in a month. I am doing this to calculate daily expenses and income. I am figuring that I will have to use a loop and calculate the number of rows that have data. Use this number to cycle through every row in a column for a certain date, and use an if statement to add data out of the other column to a variable and at the end of that date write variable to cell. Change data and clear variable and start the loop over again. Just not sure how to write this. Any help would be appreciated

Please explain your task in a little more detail.Perhaps an example of your sheet layout would help.I'm not convinced you need a macro, but even if you do, you haven't given us enough info to work with.If you use the pre tags above the comments box you can line up your data like this to make it easier to read:

A B C D E

1 2/3/2010 Lunch $10 Pay $20

2 2/4/2010 Lunch $ 7 Pay $20

3 etc.

This seems to be a day of problems.

I gave a simple Oracle select query

select * from readertable where STATUSDATE='17-MAR-07';

It didn't return any records eve though I know there is a record. The reason I know this, is because when I searched with another query on the same table

select * from readertable where READERNO=17;

it showed the STATUSDATE as '17-MAR-07'!!!

Here's another interesting quirk . When I run a date query on another table it shows the result!!!

What am I to make of all this ????? Where is the prob???

Pleeeeeeeeeeease Help!

Thanks.

I tried giving the query like

select * from <tablename> where STATUSDATE<='17-MAR-07'

and it worked ! It gave all the rows before 17-MAR-07 but at least it gave a result !

Don't ask me why , it just did .

Hello All,

This is my first time here, and I would be real grateful if someone could solve my problem.

I have a problem related to the following forum, but it goes a step further:

https://forums.techguy.org/threads/solved-automatic-mail-delivery-based-on-date-time.1138132/

Since I am new to programming, and have never done VBA, can you please guide or provide the Excel VBA code for my requirement. I could not find a relevant forum to add comment to, since all the related ones I visited are closed, so I had to create a new one.

Actually, it is for planning purposes at my end. I need to send emails based on holidays in different offices in different regions to a specific email addresses, so, people in all the offices can plan better.

I have attached a file named "Sync", having three different sheets, one having India Holidays and the other two having our US & UK office holidays.

For each of the holiday, in each of the three regions, I want to send an email 7 days prior (to the upcoming holiday) to an email address, say, [email protected]. This will help me plan the week and consider which regions will be working, and accordingly assign work.

Help will be greatly appreciated.

Many Thanks & Warm Regards

Tejeshwar

I am not sure if what I want to do is possable or not. I have a spread sheet with three colums, date, total, daily total. First of all the dates in will very and have multiple entries for the same day or just one depending on service. I want to make one entry in the daily total column on the last entry of the day with the total for the day. So i need to check dates in column date and if the same add the corrosponding row in time for one entry. And I am working with Excel 2003. Is this possable? If so can someone please help me!!

Try =SUMIF()If your data looks like this:

A B

1) 9/30/2010 1.00

2) 10/1/2010 2.00

3) 10/1/2010 2.00

4) 10/3/2010 2.00

5) 10/4/2010 1.00

6) 10/1/2010 1.00

7) 10/6/2010 1.00

8) 10/7/2010 1.00

9) 10/8/2010 1.00

Then to find the total for the date 10/1/2010 use this formula:=SUMIF(A1:A9,"10/1/2010",B1:B9)You could also do it by selecting a date from your date column like this:=SUMIF(A1:A9,A6,B1:B9)MIKEhttp://www.skeptic.com/

I have two columns of dates, and I need to apply a conditional formatting rule so that;If a date in Column B falls within 0-3 months of the date in Column A (same row), it highlights GreenIf a date in Column B falls within 3-6 months of the date in Column A (same row), it highlights YellowIf a date in Column B falls within 6-12 months of the date in Column A (same row), it highlights RedI have searched on a lot of forums, but cannot find anywhere. Any help very much appreciated! Thanks :)

Try this How-To and see if it works for you:https://www.computing.net/howtos/sh...MIKEhttp://www.skeptic.com/

2 more repliesHi there,

I have a whole column of dates in this format in my table: 20080602

I want my macro to change the format of the dates from this 8-digit number string, into a date format that Excel can recognize (6/02/2008). Once the dates are re-formatted, I then want to have the macro select rows with dates that are up to four days of age only for the new report.

Here is what I've got to format the dates. When I run it, the macro stops at this point and tells me to debug, telling me I have a 'syntax error'. A what?

Columns("A:A").Select

Selection.Format = Date(left(a1,4), mid(a1,3,2), right((a1,2))

Once I do get the dates right, how would I get it to select dates that are up to four business days in age only? This is what I thought I should use, but it doesn't work:

Range("A1").Select

Selection.Format >=today()-4

Thanks!

I don't think that you can do what you want using the Format function as you have tried to do it. You are on the right lines with the Left, Mid and Right though.

in another column put this in a Cell on the same row as A1

=mid(a1,5,2) & "/" & right(a1, 2) & "/" & left(a1,4)

If that gives you the correct "look" then Drag that formula down the rest of the rows with Dates in. Then copy the whole Range of new look dates and Paste Special>Values over the first cell in that Range or in a new column and you should now have your Dates.

In the next column type in the first cell

= if(today() - Celladdress <= 4, "Yes", "")

Where celladdress is the cell with you first "Proper" Date.

This will place a "yes" in the column wherever the date is within 4 Days.

If this is not satisfacory and you really need to have a Macro to do this you can use the same "Formatting" in the Macro, but you will require a "For/Next or For/Each loop" to do it.

Bios shows date as 1/17/2017 and laptop shows it as 17/1/2017 I can find no way of changing it.

Is this normal and nothing to worry about.

Looks as if the BIOS is displaying dates in US format MM/DD/YYYY rather than UK format DD/MM/YYYY, so it is showing the same date, just in an alternative format.

Not being able to see any occasion where you need to look in the BIOS to check the date it shouldn't really be a problem

Hi, I am trying to create an IF nested formula for this case: If it is less than X date = Over Retained and Not Over Retained if it there is no date, or the date is greater than X date.

We could use a little more information.1 - You haven't told us what you want to happen if the cell equals the date.2 - When you say there is "no date", what is actually in the cell? Is it blank or text or something else?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

4 more repliesFor example. Col Start_date 3/15/2011 and Col End_date 3/18/2011.I would like to search for the 3/16/2011

You could create a simple input query that prompts for the date you're looking for.

2 more repliesI'm looking for something as easy as Google workflowy or Microsoft WORD outline mode and includes time and date features such as starting dates and clock.

Hi,

I am trying to calculate Total Cost of Labor with fees and rates that change depending on when(date range) they are charged. I have gotten it to work, but I would like it to be more efficient.

Currently I have it set up like this:

tbl_Employee Base Labor Rates_date changes

fields:

Employee Name

Base LR_SD

Base LR_ED

Base LR

Base LR_SD1

Base LR_ED1

Base LR1

Base LR_SD2

Base LR_ED2

Base LR2

Base LR_SD3

Base LR_ED3

Base LR3

Base LR_SD4

Base LR_ED4

Base LR4

Base LR_SD5

Base LR_ED5

Base LR5

Base LR_SD6

Base LR_ED6

Base LR6

Base LR_SD7

Base LR_ED7

Base LR7

Note: LR= Labor Rate, SD= Start Date, ED= End Date

tbl_Hours Charged

Employee Name

DO #

Work Week Ending

Hours Charged

SQL

SELECT

[qry_Labor Hrs Charged Query].[Employee Name],

[qry_Labor Hrs Charged Query].[Labor

Category], [qry_Labor Hrs Charged Query].[DO #],

[qry_Labor Hrs Charged Query].[Work Week Ending],

[qry_Labor Hrs Charged Query].[Hours Charged],

IIf([Work Week Ending] Between [Base LR_SD] And [BaseLR_ED],[hours charged]*[Base LR],

IIf([Work Week Ending] Between [Base LR_SD1] And [Base LR_ED1],[hours charged]*[Base LR1],

IIf([Work Week Ending] Between [Base LR_SD2] And [Base LR_ED2],[hours charged]*[Base LR2],

IIf([Work Week Ending] Between [Base LR_SD3] And [Base LR_ED3],[hours charged]*[Base LR3],

IIf([Work Week Ending] Between [Base LR_SD4] And [Base LR_ED4],[hours charged]*[Base LR4],

IIf([Work Week Ending] Between [Base LR_SD5] And [Base LR_ED5],[hours charged]*[Base LR5],

IIf([Work Week Ending] B... Read more

I use Microsoft Office and Word 2007 and I.E. 9 in Vista Home Premium. After doing some ?fixes? for other issues, I lost the feature of Auto Complete for dates. I tried to restore to an earlier time and that did not fix it. I also checked the Auto Complete in Internet Options and ran Malware. How can I restore the Auto Complete for dates?

Thanks,

Jerry

I like to name each sheet with dates ,Like 01.01.2010 and next sheet with 01,02.2010 and so on ..... I would like to know if there is way to do it with some kind of auto fill than individually entering each dates .

Hi,You haven't said if this is a one-time requirement or a regular requirement.I have written a short macro which assumes that this is a one-time requirement, and therefore there are few frills - for example, enter text that Excel does not recognize as a date, and the macro exits - no helpful messages or offers to try again.Right-click on the name tab of a worksheet in your workbook.Select 'View Code'In the large Visual Basic window that opens, enter this:Sub AddDatedWS()

Dim strStartDt As String

Dim strEndDt As String

Dim dtStart As Date

Dim dtEnd As Date

Dim wsNew As Worksheet

Dim n As Double

'get start date

strStartDt = InputBox("Enter start date", "Create dated worksheets")

If Not IsDate(strStartDt) Then Exit Sub

'get end date

strEndDt = InputBox("Enter end date", "Create dated worksheets")

If Not IsDate(strStartDt) Then Exit Sub

'convert text to Excel's date format

dtStart = CDate(strStartDt)

dtEnd = CDate(strEndDt)

'test if start date equal to or later than end date

If dtStart >= dtEnd Then Exit Sub

'confirm number of sheets

If MsgBox("Create " & dtEnd - dtStart + 1 & " worksheets", vbOKCancel) = _

vbCancel Then Exit Sub

For n = dtStart To dtEnd

'create a new worksheet

Set wsNew = ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))

'name it with a date (date text can't contain : \ / ? * [ or ])

wsNew.Name = Format(n, "dd.mm.yy")

Next n

End Sub

Place you cursor in the macro - on the line "Sub AddDatedWS ()" will do.Click th... Read more

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

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 repliesFilename: 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

I'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 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

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

Hello,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 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 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 repliesNumber of working hours between two dates. Working hrs 6:00 AM to 8:00 PM. Including Saturday, excluding Sunday and Holidays

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

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

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

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

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 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 repliesSome 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 repliesI 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 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 repliesHi,

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 >

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

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

Thanks for your help.

Is 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?

Probably 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 repliesI 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

When 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?

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

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.

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

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

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

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

Hi 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

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