Computer Support Forum

Excel running automatic mail send for expired dates

Question: Excel running automatic mail send for expired dates

Hello, I have been having trouble parsing out the code for an automatic notification system that will send out an email when a certification is about to expire. I keep getting error messages on one or two lines of code. I have attached the code that I have so far and a dummy spreadsheet of what I am trying to accomplish. The goal is to explore the automatic email notification option for the LEOSA Expiration date (column D), the Concealed Carry Permit Expiration (column G). For column ?D? and ?G ?, when an expiration is within 90 days the box turns yellow and sends a notification email. When the expiration is within 30 days it turns RED and an email notification is distributed. At each of these milestones (90 and 30 Day) the request is to determine if an ?auto send? type process can be utilized.

Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 7 Enterprise, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM) i5-2500 CPU @ 3.30GHz, Intel64 Family 6 Model 42 Stepping 7
Processor Count: 4
RAM: 3984 Mb
Graphics Card: Intel(R) HD Graphics, 1800 Mb
Hard Drives: C: 465 GB (400 GB Free);
Motherboard: Hewlett-Packard, 1495
Antivirus: Symantec Endpoint Protection, Enabled and Updated

More replies
Relevance 100%
Preferred Solution: Excel running automatic mail send for expired dates

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

Relevance 96.35%

Good Afternoon all,

I have been trying to make my register e-mail when an expiry date is approaching. Now I've made a code to try and get this to work however I get a error 400 message.
The e-mail it needs to go to is in (A1)
The expiry dates are in row (P3:R164)
I would like it to also send the persons name in (J3:L164) within the e-mail as I havent worked this out yet.

Attached is the example workbook.

The code I am using is:

HTML:

Sub Email()
Dim objExcel
Dim objOutlook
Dim objMail
Dim objWB
Dim objWS
Dim vCell
Set objExcel = CreateObject("Excel.Application")
Set objOutlook = CreateObject("Outlook.Application")
objExcel.DisplayAlerts = False
objExcel.Workbooks.Open ("C:\Test.xls")
Set objWB = objExcel.ActiveWorkbook
Set objWS = objWB.ActiveSheet
For Each vCell In objWS.Range("P3:R164" & objWS.Cells(objWS.Rows.Count, "S").End(-4162).Row).Cells
If FormatDateTime(vCell) <= FormatDateTime(Date) Then
If vCell.Offset(0, 1).Value <> "YES" Then
Set objMail = objOutlook.CreateItem(olMailItem)
objMail.To = "[EMAIL="[email protected]"][email protected][/EMAIL]"
objMail.Subject = vCell.Offset(0, -8).Value & "ChemAlert Password About to Expire"
objMail.Body = "COURSE TITLE - " & vCell.Offset(0, -8).Value & vbCrLf & _
"APPROVING STATE - " & vCell.Offset(0, -7).Value & vbCrLf & _
"APPROVING BODY - " & v... Read more

Answer:Excel coding to send an e-mail on expiry dates.

Hi, welcome to the forum.
Have you run a search on this forum, there are many posts with similar questions and answered too.
All you will need to do is probably edit it for your needs.
See if you can find these, no need to reinvent the wheel
 

3 more replies
Relevance 81.59%

Please help if you can, I know very little about excel and I have a very complicated spreadsheet to build.

If I can pull this off, it will help me fight the coming layoffs.
Problem

I need excel to send automatic email when the spread sheet is opened to the supervisor every time a check in date hits and to keep emailing them everyday until they mark the cell next to the date as completed. I need to learn VB for this and I am running out of time!

Same thing for the shift manager complex manager and HR.
Am working on the small stuff but am struggling with the emails.

I really need some help and fast, I can repay the favor with translations, building basic spreadsheet or let me know what I can do to pay you back for helping. I really help please. I will give you my cell number in PM if needed.
If possible, I need to have this done by 3pm Monday 01/08/2016.

Spreadsheet update
 

Answer:send automatic email on multible dates until checked off completed.

6 more replies
Relevance 78.72%

I'm trying to enter a range within a cell: 10-14. When I press Enter, Excel automatically changes the content of the cell to read Oct. 14. The higher ranges, 15-19, 20-24, etc. are fine because Excel doesn't interpret the first 2 digits as a month. How can I stop Excel from automatically entering the date in place of the typed range?
Thank you for your help.
 

Answer:Excel XP: Stop automatic insertion of dates

cbsy said:

I'm trying to enter a range within a cell: 10-14. When I press Enter, Excel automatically changes the content of the cell to read Oct. 14. The higher ranges, 15-19, 20-24, etc. are fine because Excel doesn't interpret the first 2 digits as a month. How can I stop Excel from automatically entering the date in place of the typed range?
Thank you for your help.Click to expand...

Either precede the data by an apostrophe or format as text befrore entering data. None of which is rather appealing.
 

2 more replies
Relevance 77.9%

Hello,

I will be grateful if you can please help me have automation in the attached Excel sheet for automatically generating and sending (triggering) Emails to the action owners based on their Email IDs upon approaching of "Actual Finish Date (column I) in the attached Excel sheet? My request is similar to https://forums.techguy.org/threads/excel-sending-automatic-email-to-owners-near-due-dates.1171902/.

You are welcome to suggest and implement value additions.

It will be a boon if this task can be accomplished irrespective of any Email client. Be it Microsoft Outlook or Lotus Notes. Please help.

Regards,
Maitreya Barve.
 

More replies
Relevance 77.9%

Hi, I have a risk register which has action by dates for owners of that particular risk. I would like to set up a VBA code that automatically sends each owner an email reminder, through outlook, 5 days before the action date. One owner may have multiple risks due on the same date so I would like the code to send 1 email detailing all the item numbers that the owner has to look at (however this is not at the top of my requirements if it causes issues). I would like the email to include the "Number" (Col. A) in the body listing which risks the owner should look at along with a message reminding them that the action by date is near. Also i want this to be automated, I want the code to run once and send emails as time goes on. Also i will need to change the action by dates as new information is available, can the code account for this change and send another email when the new action by date is near? Thanks for your help I really appreciate it. Using excel 2010
 

Answer:Excel sending automatic email to owners near due dates

12 more replies
Relevance 77.9%

Hi, this is my first post here and I will preface this by saying I am not familiar with VBA/VBScript language at all (only that it exists and perplexes me), which is why I'm requesting your help!
I have an Excel spreadsheet that I'd like to have send me an Outlook email reminder 6 months after the specified start date, without having to open the Excel file so the macro can run. The reminder email can be simple, just a short message like "Referral Alert: Check Spreadsheet"

I've attached a test file with the columns needed highlighted in yellow.

If this is too tall of an order, I understand!
 

Answer:Send Outlook reminder on specified dates from Excel

As far as I know, this is impossible in the given scenario. Macros only run once a file is loaded; therefore, not having the file loaded means any code won't execute.
 

2 more replies
Relevance 77.08%

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!
 

More replies
Relevance 77.08%

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.
 

More replies
Relevance 76.26%

Hi I would like to get VBA/macro codes to send an automated email to the email IDS mentioned in the file when the invoice due date is less than 2 days of current date. please help me
 

Answer:Excel 2016 to send Outlook email reminders on various dates

Here's a similar thread on the forum. If you can follow the code, then you can adapt it to suit your needs.
 

1 more replies
Relevance 76.26%

I'm in HR and I have a spreadsheet that incorporates staff information commencing, with each month in a new sheet. Unfortunately, department managers are forgetting to do staff reviews at 3mth, 5mth or the 6mth probation. I've entered formula to calculate these dates from the staff commencement date.
Now I need to find out if I can have some sort of Macro or VBA coding to email me a reminder to contact the managers a week prior to the the review/probation dates.

Please help! I have no idea with coding/programming etc.
 

Answer:Excel 2016 to send Outlook email reminders on various dates

Try the attached, one thing to note that you had the probation dates in the wrong place

6mth, 3mth and 5mth

so I changed it to 3\5\6

when you open the workbook the macro will run and generate an email IF any dates is below or equal to 7 and above or equal to zero. Meaning that there is a week until the review is required. This code will fail if the review date is in the past, this can be changed to tell you that a review date has been exceeded.
 

1 more replies
Relevance 72.57%

I am having problems mail merging dates from an excel spreadsheet into a microsoft word document. E.G. in excel it is displayed as04-May-13 when merged it comes out as05/04/13 in the word document. How do I display it correctly???Frustrated......

Answer:mail merging dates from excel into word

Make sure you computer is configured correctly for European dates format. Control Panel > Regional Setting. Configure short Date for the format you want the dates to display.Stuart

2 more replies
Relevance 71.75%

Hi All,

Let me take the pleasure to introduce myself as Vasu, beginner in this forum.

I know that there are many on going threads related to my this new thread. But, actually I had gone through some of the posts (like Rollin, OBP, and Diego) as per my need and I did saw OBP used to share some links which already covers this my new thread, but since I am totally beginner to MS Excel, so I could not understand many of the things. So, with left chance I thought initiating the new thread, so that I can aware of step-by-step to "automatically send an email from excel on date basis". Hope you all fine with this.

So, here is what I need, I have a sheet (which contains columns Request No, Owner, Run Date, Due Date to Close Request). Usually sometimes we miss to close the requests as per the due dates.

So, could you please share detailed information on how can my excel automatically send an email whenever the "Run Date" crosses??

As per my understanding after reading the existing posts, I thought of giving you some sample data from my side. In my attached workbook, there are two sheets ("Request Tracker" and "Email"). "Request Tracker" sheet contains the base data on which "Email" sheet contains what I need in my email when excel send an email.

I would be more than happy to give you any additional information if required.

I use MS Outlook and MS Excel on Windows.

Thanks for your assistance and help to get my problem ... Read more

Answer:How to send an automatic email from excel?

15 more replies
Relevance 71.34%

i want to set up an e mail that will send automatically to my contacts using a microsoft outlook in a certain time of the day?how to send an automatic e mail using microsoft outlook in a specific time of the day?

Answer:how to send an automatic e mail in a certain time of the day

schedule outlook email to send laterhttp://is.gd/KepIFX

4 more replies
Relevance 70.93%

0down votefavoriteCould you please help me to automatically send an email from Excel only when the formula value in column M (=IF(VAL.EMPTY(K15);"";MAX(K15-Today();0))>200. Unfortunately the Sheet1 code triggers the email code if the condition is met (>200) in formula value cell in column M if the date in column K is altered manually or by writing manually Not Sent in column N. Instead my goal would be: 1) to understand why this code in sheet1 doesn't send the email automatically as supposed to do (the only thing it does is to put Sent in column N without sending the email. This make me think that this code works) 2) to find the way to send the email automatically without changing anything manually in the cells in my sheet1. H I J K L M N Date Score Description Next Due Status Days till expiration 15 28/09/2017 13 Medium Risk 25/07/2018 Valid 284 Sent 16 11/10/2017 13 Medium Risk 10/08/2018 Valid 300 Sent 'Sheet1 (FormulaValueChange)Private Sub Worksheet_Calculate()Dim FormulaRange As RangeDim NotSentMsg As StringDim MyMsg As StringDim SentMsg As StringDim MyLimit As DoubleNotSentMsg = "Not Sent"SentMsg = "Sent"'Above the MyLimit value it will run the macroMyLimit = 200'Set the range with the Formula that you want to checkSet FormulaRange =... Read more

More replies
Relevance 70.11%

Basically, I have created a very simple Excel spreadsheet as an example, but what I would like to do is the following:

I have several employees (100 +/-) that require training in various fields. Each training certification is good for 1-yr. I am trying to figure a way for Excel to automatically send an email to my Microsoft Outlook whenever that training date is set to expire. I would like to have it email me 30-days before it expires. The problem is that I don't record and notate it by the date the training expires, but rather by the date they were trained. An example would be that I trained someone on 5-3-13 and they will be expiring 30-days from now. I have it entered on the spreadsheet as 5-3-13. How can I make Excel automatically generate an email warning me of the upcoming expiration date? I am admittedly not very proficient in computer language, but I am more than willing to learn.
 

Answer:Trying to send automatic email notification from Excel 2010

6 more replies
Relevance 67.65%

I'm trying to create a report in my Access Issue Database that will return only those records that have an EXPIRED DUE DATE.

I'm a little familiar with the Expression Builder, I'm just not sure what exactly to put in my thread.

Any help is greatly appreciated!

Shelly ~ Knowing just enough to get myself into T-R-O-U-B-L-E
 

More replies
Relevance 67.24%

Hello Techguy!

I noticed a thread from earlier on, although I am still a little in the dark

I am making an interactive checklist with a deadline timer, and when this timer goes too low, I'd like excel to send an e-mail to me. Much like the thread in http://forums.techguy.org/business-applications/710581-automatic-email-alerts-using-excel.html.

I know it's in Danish, but this is my workbook.

Now the words don't really matter The text I would like to get e-mailed is the "I" culoumn.
Also please do not change the 2nd sheet (Ark2).

OS is XP, Excel is 2010 and I have Outlook 2010 as well.

Your help would be much appreciated!
 

Answer:Automatic e-mail from excel

16 more replies
Relevance 67.24%

hi everyone, this is my first post on techguy. I really need help with a Microsoft Excel spreadsheet. The workbook consists of gauge numbers and their calibration due dates.

Can someone please help me so that Microsoft Excel automatically sends a mail to a particular email address when the due dates are about to pass in a day/have already been passed?

The mail should state that the following gauges are due for calibration.
Any help will be appreciated. thanks
 

Answer:Excel automatic mail.

16 more replies
Relevance 67.24%

Hi,

Iam new user to this site and also New user to Excel application.I want your guidance in the below -

I want to know how to send automatic mails from excel(a range of cells to be copied and must be pasted in body of the mail and send mails to defined mail ids).
Is that possible?
Need - Every month i want to share some reports/values with some group of ids.So what iam doing is collecting the data from different sources and merging in one single excel.Upto here is ok with me..coz its diffiult to automatize collecting information from different sheets.
Then i will copy some range of values and sends to paste it in body of outlook mail and sends to the group of ids.For this i want solution.Is there any option in excel that i can click or run.so that it automatically sends that range of values to defined mail ids in body of mail.
It will be greatly helpful if i got solution to this question.
Iam using windows7 licensed version.MS Office 2013.I cant load any third party/non licensed version softwares in my laptop.

 

More replies
Relevance 66.42%

Hello all,
I am new to Tech Support Guy and to macros/VB code. I hope my question make sense! I have looked at other threads
but I don't even have the understanding of which bits of code are adaptable to my needs.
My colleagues and I run Office 2003 (with Excel and Outlook) and share a spreadsheet of client data to allow us
to co-ordinate our business with them.
At present our work is overly dependant on individuals remembering to action a task, and on paper lists in
different places. I want to try to bring us up to at least the 20th Century!
Our principal concern is when paperwork is due to expire and needs sending off by post to another team
to re-validate the paperwork (column M). I would like the spreadsheet to keep track of when paperwork is expired and to send
an e-mail alert to myself and my colleagues to make sure that we can get it sorted.
The e-mail would say something like:
subject: 'Charts for signing'
Main text: The charts that require new signitures are:
...and then a the rows of the spreadsheet listing the clients that have expired paperwork.
I enclose the spreadsheet with a couple of fake examples to try to explain what I mean. In this example I would want the
information in the bottom row (or at least the first three columns from this row) to be detailed in the email. I would not
have the ability to install software that could help with the process.
Each time we had re-validated paperwork sent back to us the spreadsheet could be updated... Read more

Answer:Automatic e-mail sending from Excel

Hi, welcome to the forum.

I'll have to think it over but meanwhile I've downloaded the file and will see if I can offer any ideas. Don't hold your breath
 

3 more replies
Relevance 66.42%

hi guys;
i want to make an automatic e-mail sending function into my excel worksheet,but i dunno ho to do it.
The basic thing is as soon as i put the social security number into one cell then excel should send an email this worksheet to me.
I would like to hear your ideas, thanks.
 

Answer:Automatic e-mail sending in Excel

Though I'm not quite sure what you're trying to do.
This articles by Ron Bruin an Excel MVP should be useful to you. Click on the link. http://msdn.microsoft.com/en-us/library/aa203718(office.11).aspx
and
http://msdn.microsoft.com/en-us/library/bb268022.aspx#Excel2007DifferentWaysEmail_SendingtheActiveWorkbookbyEMail
Please read the article and tell me what you think.
 

3 more replies
Relevance 66.42%

Dear all,

My name is Mohamed, i am working in Polic Department, we are issue a Drive License for our people.

I made an Excel sheet file called ( DR.xls). this excel sheet have only one sheet with it called ( DR-INFO ).

This sheet, have 8 colums as following :-
1-#
2-Drive Number
3-Day of Issue - Di
4-Month of Issue-Mi
5-Year of Issue-Yi
6-Day of Expire -De
7-Month of Expire- Me
8-Year of Expire-Ye

and i am writing all the data on this excel sheet.

I want to do something, because the number of Drive Lilcesnce is about 700 .

What i want to do, is, i want to write a code to do the following :-

on every day @ 10:00 AM , the excel sheet will open the file which is called DR.xls, and will look to only three colums which they are day off expire, month of expire, year of expire. and then to compare the date of today, and in case of the date of what is looking to is equal to what is in the excel sheet, he will send an e-mail to notify the follwoing with the drive license number , and date of expire , to the following :-

[email protected].

i am using MS-Outlook 2007.

can anyone please help me by this.

regards,
MT
 

Answer:How to send an e-mail from excel

Mohamed, welcome to the Forum.
Have you looked at this Thread?
http://forums.techguy.org/business-applications/856705-automatic-email-excel-based-date.html
 

1 more replies
Relevance 66.42%

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?

Answer:How can I auto-populate dates in Excel based on other dates?

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 replies
Relevance 66.01%

When using Ron de Bruin CDO_Mail_Workbook to email workbook from Excel, all works great when opening the file in Excel. However we have the need to post the file on the intranet and in some cases it will open embedded in IE. At this point we get the following error:

1004 Method 'SaveCopyAs' of object '_Workbook' failed.

It appears that since it is embedded the VBA does not know the ActiveWorkbook information.

Thanks
Josh
 

More replies
Relevance 66.01%

Is there a way to have Excel 2007 to automatically send a document by e-mail (not an XLS file)?
 

Answer:Solved: Excel 2007 Automatic E-mail

The below is a cut-down of this code.

Note that (i) client is Outlook (ii) early binding requires setting a reference to Outlook in Excel's VBE before use.

HTH

Sub SendWithAtt()

Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim CurrFile As String

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

With olMail
.To = "[email protected]"
.Subject = "This is the subject"
.Attachments.Add "C:\Documents and Settings\Andy\My Documents\test.doc"
.Display
.Send
End With

Set olMail = Nothing
Set olApp = Nothing

End Sub
 

3 more replies
Relevance 65.6%

how do you do mailto and send within a macro?
receiver, subject and body would all be static.

Thanks in advance,
Eric
 

Answer:Solved: How send E-mail in excel using VBA

9 more replies
Relevance 65.6%

Hi all,

This is my first post. Essentially my problem is one that is no doubt relatively straight forward, however I am an absolute novice (as in no experience) in VBA. I have a multi-sheet workbook that is being used for people to enter their work times (each sheet is a different employee, with each row being a separate day). I would like (if possible) to have an email sent automatically to an end user if they haven't completed their time sheet from the day before. I have the IF function in place so that a cell will trigger either send of don't send depending on the value of the time cell at the end of the previous day "=IF(AND(B10=TODAY()-1,(F10=$H$1)),("SEND"),("DON?T"))" where B10 is today's date, and F10 is a blank time.

I have each employees email address listed on their individual sheet. Essentially I feel like I have all the necessary components together (the easy bit) I just don't know how to put it all together.

Any help would be greatly appreciated, even more so if you could explain what is actually going on!

Cheers

Stuart
 

More replies
Relevance 65.6%

Hi guys,

could anyone help with this excel!
I know there are similar strings available here, but not what I am looking for!
I need to know how I can get one of my column (ie: if column G turns to 'overdue') then sends out an e-mail to a service engineer automaitcly with a message 'item is over due servicing'. could this be done, anyone who can help I would really appriciate.

I am very new to macros and VBA. please provide a detailed (just step by step would do) explanation please?
thanks,
visNOTkid
 

More replies
Relevance 65.6%

hi, i have an excel application that has expiration dates, now what i wanted to do is to send notification emails to the respective recipients lets say 60 days prior to the expiration date or just alert me 60 days before the expiry date and then i could just send them e-mails myself.is there a way i will be able to do that?thanks!

Answer:alert and send e-mail using excel

Hi'Look at this post, response 7 andthis post.Both create and send e-mails from Excel.Note the warnings about the use of passwords in the code and potential security risks - less of an issue if you are not distributing the worksheet to anyone else.Have a go with the concepts in these posts and post back if you need more specific advice.As to the warning, as an example, if Cell C13 has an expiration date in it, then this formula displays a warning if the date is less than 60 days from today. If it is 60 or more days, it returns a blank cell:=IF(C13-TODAY()<60,"Send email","")Regards

5 more replies
Relevance 64.78%

I am learning access and have build a database. in my tables we have dates to track employee expiration dates and some tables that show due dates for maintenance on equipment. Field names look like this.
LicExpires, MVR, AR, and so forth...
On one of my queries for equipment due dates, I used this formula in the query; InspDue: DateAdd("yyyy",1,[LastInsp]), which meant that I had to add another field "InspDue".
On my employees query I have like 10 fields that have due dates in them and I was wondering how can I just get the names of the employees to show up with any one of these fields with due say like today and/or 30 days in the furture?
I have tried everything that I have read to make this work but when I use the >Date() expression I get back blank query.
I am to my witts end here.
Also in the same table, if the field is null how can I get it to show on the same query?
Any help is appreciated.

Thanks,

David
 

Answer:Solved: Access 2007 Query/Report to show expired dates

16 more replies
Relevance 64.37%

HI, Its my first post here. I need to send reminder mails from outlook based on excel reminder dates.
For this i should not open excel neither i know VBA scritping for this.
Please provide help on this.
As i attached my file i need list of assets need to be calibrated ahead of 45 days of calibration due date.
 

More replies
Relevance 64.37%

Hi,

i have used the following to get automatic alerts setup when a due date is near:
https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/

but instead of this looking at the due date column, i would like it to look at a conditonal formatted column, which shows status as either "due" and "expired" depending on due date. i would like it send out to all those with "due tag and not "expired"

I have name in column A, details in column B, emails address in column C, status in column i, and reminder in column k
Somethng else i would like to do later on, if at all possible,is to get a particluar email from outlook be transfered to excel and and then emailed to specific people based on a status column. So if an email is recieved to say a project is extended or cancelled,, it contents is copied to excel and emailed back out to certain people.
Thank you for your support.
 

More replies
Relevance 64.37%

Hello Forum member,

I am working on an excel file with hundreds of records and tens of columns. There is a column"L" which gives the date when the license key given in column "B" of a particular software is going to expire. I need to check the column and send email to Person X [email protected] 3 months in advance so that he can take poper action to get the license renewed. It is too much of a manual work every week.

I am looking for a macro that can send an email to person X stating that license key value given in coumn B is due to expiration in 3 months from today(based on value given in column L)

I have seem a similar thread in the forum giveb below but i need changes to it. Unfortunately, the thread is closed and i can not contact the person who gave the solution. I am not able to modify the code shared to match my requirement.
Automatic Outlook mail due date reminder based on Excel file

http://forums.techguy.org/business-applications/1129238-automatic-outlook-mail-due-date.html
Can anyone please modify the code given in the above thread to match my requirement. Please!!Thank you!
 

Answer:Automatic Outlook mail based on a column value in excel file

8 more replies
Relevance 64.37%

Hi,

I am trying to send some reports to a set of IDs. Referring to the enclosed excel will help understand the issue.

Column A has the set of IDs to whom mails have to be sent.
I need to filter out the data pertaining to the IDs and send only relevant data to respective people.

For eg: Lets take the vakue 1 in column A. I want excel to filter out 1 in column A and send the details from B1 to I5 to the ID 1. Similarly for ID2 and so on. I use microsoft outlook to send emails. Outlook recognizes these IDs, so the email addresses are not required to send the mail. Just ID would do.

Can some content also be added, apart from just providing the data.

Is this possible and if so can someone please share the code for the same. Im just a beginner. Looking forward to some assistance.

Thank you.
 

Answer:Send mail from excel to unique values in a particular column

Welcome to the board.

1. I checked Ron de Bruin's site, his Mail Range or Selection code looks like it'll do the job with a little tweaking for your requirements.

2. For the tweaking to work, the data must be pre-sorted by column A.

3. To run the code, you must select a data cell (i.e. any cell in A2:I10) & then click the "Mail Range" button.

4. The tweaking is: (i) a few lines between "added code" & "end added code" (ii) the Set Source = Range line (iii) the .To = Addressee line.

5. There's also a line .Display ; needs to be changed to .Send if automatic sending is required. Near that line are others such as Subject = "This is the Subject line" and Body = "Hi there", adjusting those would cover "Can some content also be added?".

6. To view the code, press ALT+F8, highlight Mail_Range, click on "Edit".

HTH
 

2 more replies
Relevance 64.37%

I just got a new computer at work. On my old computer, which has the same operating system and software I am currently using, I was able to send a workbook directly from MS Excel into Lotus Notes. I have Office 2003 and Lotus Notes 8.5. My LAN admin is at a loss on how to get this to work. I added the Mail Recipient (with Attachment) from the customized command menu but it is greyed out - PLEASE HELP

I know this works, it did before but no can here can figure out how to make it work again.

I can get the 'Send for Review' function to work so why don't they both work?
 

More replies
Relevance 64.37%

In Excel 2010 I use the Review tab to hit the Mail button which opens my Outlook-attaches the spreadsheet and sends the email with the Spreadsheet successfully. However there is no message text in the body. No matter what I type (e.g. Mary, how are you today?) it just sends a blank email with a good attachment. Any ideas?

More replies
Relevance 64.37%

Hello, I am having trouble sending to mail recipient as attachment in Excel 2002. I get two error messages. They are: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client. I try to send again and I get this: General mail failure. Quit Microsoft Excel, restart the mail system, and try again.

Send to mail recipient works as it should, the send as attachment does not.

I made sure Outlook is the default mail client in Outlook and IE. I tried rebooting and the same errors occur. I have tried multiple excel files to no avail. The same thing happens in WORD.

Short term solution is saving the file to the hard drive and then opening Outlook and attaching the file that way.

The OS is XP and the office suite is XP/2002. Let me know if you need anything else.

Thank you - Scott
 

Answer:Excel 2002 cannot send to mail recipient as attachment

Have you run Detect and Repair from Outlook's Help menu?
If that doesn't work, try running Repair on the whole Office suite from the control panel.
 

3 more replies
Relevance 63.96%

Hello all,

I'm receiving mail alert based on date only, it is not considering the month into the account.. Kindly suggest..

Attached the excel file with macro...
 

Answer:Automatic Outlook mail due date reminder based on Excel file

16 more replies
Relevance 63.96%

Hi Everyone!

This is my first post here. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a payment is 10 days away from the current date. The script should preferably run automatically every time the PC is running. without the excel file necessarily open.

In the attached excel file, An email should go of to -email address (Col. Q), with subject "Vendor Payment (Col. C) is due on Due date(Col. M)", and body "Dear Name(Col. P), please update the payment status".

Also, the script should put a check mark on Reminder sent column (Col. Q) after the mail is sent, the script should also check if the value of the cell is blank before sending email.

I have scored the forum for similar problems, and although I found a number of threads, I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,
 

Answer:Automatic Outlook mail due date reminder based on Excel file

15 more replies
Relevance 63.96%

Hi Everyone!

This is my first post here. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a project is 7 days away from the current date. The script should preferably run automatically every time the PC is running. without the excel file necessarily open.

In the attached excel file, An email should go of to -email address (Col. D), with subject "Project (Col. B) is due on Due date(Col. C)", and body "Dear Name(Col. A), please update your project status".

Also, the script should put a check mark on Reminder sent column (Col. E) after the mail is sent, the script should also check if the value of the cell is blank before sending email.

I have scoured the forum for similar problems, and although I found a number of of threads,I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,

Thanks
 

Answer:Automatic Outlook mail due date reminder based on Excel file

8 more replies
Relevance 63.55%

System: Windows Vista and Microsoft Office 2007

When I Right-Click an Excel file, go to Send To, then select Mail Recipient, to send it to an email addressee, as I type the first characters of the address it automatically fills in a previously deleted email group. I have deleted an Outlook Email Group and renamed and restructured it, but the system keeps inserting the old deleted entry. Since the separate addressees are still in the system, it remembers and inserts them via an erased group. Its memory is too great! How can I purge this from memory and utilize the New, Improved email groups? Lastly let me say, Thank you in advance.
 

Answer:Email; Excel (Right Click) “Send To” – “Mail recipient” problem

Hi BudParker

Outlook automatically inserts, the e-mail group, in the To: field? It doesn't give it as an option in a drop down list?
If it is appearing in a drop down list, try hitting the Delete key, when the e-mail group is highlighted.
The autocomplete list is stored in an .nk2 file for Outlook 2007.

http://www.slipstick.com/config/backup2007.asp
 

2 more replies
Relevance 63.55%

Hi all,

I know there has been a lot on this subject (so apologies) but as my knowledge in this area is limited and as I can't find anything that matches my exact needs I am struggling.

Basically, as part of my job I manage records in the office but I sometimes forget when things needs renewing etc.

What I would like is a for an e-mail to be sent through and to my outlook when a date has been reached.

Please see the attached: CSCS.xlsx

If CSCS tests had to be done every year for example, Paul Smith would be due another on 01/01/2014. I would like to set up a code so that when it got 2 weeks before that time, I got an e-mail saying 'CSCS Test due' and 'Smith' (i.e.. cell C1 and Column A for reliant person, in this case A3).

Is this something that is do-able and easy to set up?

Thanks, charli
 

Answer:Solved: Excel Send E-mail (via Outlook) When Date Reached

10 more replies
Relevance 63.55%

Hello all,

Am hoping someone may be able to help me with a task I am trying to complete.

I have a work book that I need to edit on a daily basis, and I ideally want to be able to send an email to specific email address when a date is input.

I have attached a copy of the workbook for perusal.

To try and explain - rows 3 to 11 contain data that I would want to be emailed to the email address listed in column c (drop down menu). But what I want to achieve is that the email is sent as a meeting/apt for the user listed in C (from the drop down menu of email addresses). But I want that meeting/apt to be made for the date in the BOOKED TO field.

I appreciate that this may not be possible as an automated process, so if it is possible, to have a 'send mail' button configured at the end of each row to send an email to the recipient that would also help.

I have done a fair bit of searching and looking at various bits of code - but I will have to be honest and say I have no idea on how to implement the code into what I am trying to achieve.

If anyone is able to spend time looking into this for me I would be ever so grateful - appreciate all are busy!

Many thanks in advance.
 

More replies
Relevance 63.55%

Hi:

Friend with a problem: Whenever using Word, Excel or Adobe, and using the option to send the file as attachment, the attachment sends correctly, but any text written into the message does not display, nor does a copy of the email go to the MS Outlook "sent items".

Sorry if this seems vague, but it's all I have. When sending attachments from MS Outlook, every thing is fine. It's just when using the 'send to' option from other programs.

Anyone ever seen this?

Thanks in advance.

Max
 

Answer:Excel, Word, Adobe 'send to mail recipient' option

7 more replies
Relevance 61.09%

in the past I have had no problems opening the mail l ICON and(and selecting(send link,send page,new message) now all of a sudden when I click on any of these nothing happens(nothing opens) i tried other browsers with no problems. I can select read mail,however that works when I select Outlook it says I am missing MOSERES.DLL. I dont use outlook so this is not a problem
 

Answer:Can not open (send mail,send link,send page) [moved from win98]

What program were you using mail before? If you go to Tools>Internet options>programs

see what is selected as your program for email. The only choices are Outlook,Hotmail< or Outlook express
 

2 more replies
Relevance 60.68%
Question: Automatic up-dates

Hi everyone, I need to switch my MS updates off.Could someone remind me how. thank you.

Answer:Automatic up-dates

If you are running XP go to START > SETTINGS > CONTROL PANEL > SYSTEM > AUTOMATIC UPDATES. CW

10 more replies
Relevance 59.86%

How do I turn off automatic updates in Win 10. I want to revert to an earlier version of Win 10 which I have on a backup drive but as soon as I put it onto my boot drive C it updates itself. I don't really want to mess about in the registry and am hoping that there is an easy solution that actually works.

More replies
Relevance 59.04%

Hello,
I'm new here and need some help. I am very green in VBA and need some help with the code to create a macro that will send email reminders to my team members automatically when a date passes. I've looked through the forums and have found similar solutions, but have yet to make any of them work with my spreadsheet. I have attached my spreadsheet here and will tell you a little about what I need it to do. I will input my Project name, number, and task description into the columns A-C for the associated projects submittals we receive. Then, I will select the team from drop down boxes in columns D, F, H, and J. These populate from sheet1. Once the team is selected, the corresponding emails will be placed into columns E, G, I, and K (hidden columns currently). I will then input a date that the task was received in column L, and columns M and N will autofill for a mid due date(5 days after receipt) and a due date(10 days after receipt). (colors currently change when each date passes using Conditional Formatting)
So, what I need is for a short email to be created when a date is put into column L which pulls the data from the rest of the sheet. I would also like it to send a reminder when the mid due date passes (column M) and a final reminder on the due date(column N). (So when the date color changes I would like an email to be sent) I want the email addresses to be pulled from columns E, G, I, and K, I want the subject to be "(Col.A) (Col.B) Task Request",... Read more

More replies
Relevance 58.63%

Hi all i apologize for my not understandingof VBA and repeatedly asking for working code even tho it has been explained many times before.
It somehow just dont work for me when ever i try to edit a code i mess something and it doesn't work
I tried google doc. add reminder and its great help ,but unreliable somehow and most of all i dont like posting sensitive business info further more since i have a lot of new info added i have to constantly re-upload and reassign reminders.
My job is a middle man between two parties with whom i have to follow contracts on both sides
one is 35 days before second reminder should be 31 day before actual date .

So can someone please post me code where
1 first reminder is sent 35 days before due date based on value in column C
2 second reminder 31 days before due date(column D)

emails should be sent only to one email adres

subject should be reference number (column A) from the same row as date
and body should be entire row containing date in question.

i would much appreciate help with this
,losing track of a single contract is huge penalty and things just pile up

At home i use libre office and at work Microsoft latest office, so since im writing this from home file is saved as xls office 2003 i hope this doesn't make things more complicated.

Thank you all in advance for help

Peka
 

More replies
Relevance 58.63%

I find that macros work great for certain queries and automating emailing those queries.

But I'd like to have them automatically run if possible (monthly), is that something anyone has done before?

I haven't seen anything in the Macro design that does this, but I might just be overlooking it.

Thanks
 

Answer:Access macro - automatic monthly run dates?

This should be possible in VBA rather than a macro, but anything you do will rely on the Database being "open".
 

2 more replies
Relevance 58.63%

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
 

More replies
Relevance 57.4%

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

Answer:converting excel dates in Excel 2003

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 replies
Relevance 56.99%

Is it possible to send an automatic email as a reminder on chosen dates from a field of a table with a message from another field from the table? If so how?Example: automatically email birthday dates as a reminder from an access database to outlook.

Answer:Access database to send emails on chosen dates

You can ..You will need to write a select query to extract the fields you want in the reminder from a table where the date (e.g. birthday) is today: Datefield =Now()Then use a macro to do a send object command a send the output of the select query via e-mail. Hope this helps.

4 more replies
Relevance 54.53%
Question: Excel dates

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

Answer:Excel dates

Hopefully this guide will help.

2 more replies
Relevance 54.53%
Relevance 54.53%
Question: Excel and dates

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

Answer:Excel and dates

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

24 more replies
Relevance 54.53%
Question: Dates and Excel

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

Answer:Dates and Excel

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 replies
Relevance 54.53%

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

Answer:Dates in Excel VBA (again !) ...

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 replies
Relevance 54.53%
Question: excel dates

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
 

Answer:excel dates

What is your problem and/or question?
 

2 more replies
Relevance 54.53%

I 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
 

Answer:Need Excel HELP with Dates

7 more replies
Relevance 54.53%
Question: Excel and dates

Hi 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

Answer:Excel and dates

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 replies
Relevance 54.53%
Question: Excel Dates

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

Answer:Excel Dates

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 replies
Relevance 54.53%

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

Answer:Excel - between two dates

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

10 more replies
Relevance 54.53%
Question: Excel Dates

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

Answer:Excel Dates

12 more replies
Relevance 54.53%
Question: excel dates tip

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

Answer:excel dates tip

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 replies
Relevance 54.53%

hi,I have a new laptop with vista operating system. I quite like the system but when I use the 'send to'facilty to send a photograph or word doc, the e-mail pops up with the file attached but I can't locate the send button? The send button is present on the windows mail and works normally. Anyone have any ideas? I have noticed others with vista on other sites asking for help with this problem, bt no-one has a solution.Please help it's driving me nutsSherrymanLiverpool

More replies
Relevance 54.53%

Hello,

I am currently attempting to setup an inventory file in excel to send myself and another colleague anytime an item in that inventory is to expire. I have found a command set from an old comment thread that I have been using as a template but I am not well versed in basic and cannot find where the all of the errors are occurring. So the worksheet utilizes columns a-q, with column Q where the date and time of the sent email will go. I have column A as the item to expire, and column K as the date of expiration.I currently have column p as the location for the email address to be sent but I would rather build the email address into the code since it will only be two email addresses used for the notification. Below is the code that I have been working on but it isn't working for me. Thanks in advance for any and all help.

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 Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With

Sheets(1).Select
lRow = Cells(Rows.Count, 16).End(xlUp).Row

For i = 2 To lRow
toDate = Replace(Cells(i, 11), ".", "/")
If Left(Cells(i, 17), 16) <> "Mail" And toDate - Date <= 7 Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

eSubject = "Test Article " & Cells(i, 1) & " expires " & Cells(i, 11)
eBody =... Read more

Answer:Automated Email for Expired Inventory in Excel

16 more replies
Relevance 54.12%

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.

Answer:nested if excel with or and dates

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 replies
Relevance 54.12%

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

Answer:creating dates in excel

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

2 more replies
Relevance 54.12%

I 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

Answer:Excel if then statements using dates

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 replies
Relevance 54.12%

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

More replies
Relevance 54.12%

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

Thanks for your help.
 

Answer:EXCEL - Filter Dates

8 more replies
Relevance 54.12%

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 >
 

Answer:Excel Formula - Dates

11 more replies
Relevance 54.12%

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

Answer:excel if then statements with dates

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 replies
Relevance 54.12%

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?
 

Answer:Dates in Excel 2000

9 more replies
Relevance 54.12%

Hi 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

Answer:Emailing from Excel on certain dates

16 more replies
Relevance 54.12%

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
 

Answer:Solved: excel dates

9 more replies
Relevance 54.12%

I 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

Answer:Excel counting dates

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 replies
Relevance 54.12%

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

More replies
Relevance 54.12%

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

Answer:EXCEL If Statement Using Dates

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 replies
Relevance 54.12%

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!

Answer:Excel if statements with dates

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 replies
Relevance 54.12%

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

Answer:dates in columns on excel

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

2 more replies
Relevance 54.12%

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

Answer:If Statement in Excel with Dates

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 replies
Relevance 54.12%

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

Answer:Adamant Excel Dates...

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 replies
Relevance 54.12%

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

Answer:If and Then Statements with Dates in Excel

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 replies
Relevance 54.12%

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?

Answer:Formatting Dates in Excel

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 replies
Relevance 54.12%

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!

Answer:Need help comparing dates in Excel.

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 replies
Relevance 54.12%

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?

Answer:Working with dates in Excel

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 replies
Relevance 54.12%

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

Answer:Excel and Formatting Dates

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 replies
Relevance 54.12%

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?
 

Answer:Solved: Dates in Excel

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
 

3 more replies