Computer Support Forum

Solved: Reminder date in Excel sends an e-mail

Question: Solved: Reminder date in Excel sends an e-mail

Hello.
I have been asked to send warning e-mails to a short list of adresses from an Excel file where reminder dates correspond to the actual date, and I have no clue how to do this with Macroís. Iív tryed to understand how some examples of macros dealing with Excel and e-mails with no success as I still have a lot to learnÖ

Here is my problem : I have a spreadshhet containing company names (col.A) and contract renewal dates (col.D). there are about 80 companies listed. When the actual date corresponds to the reminder date an e-mail containing the company name, a fixed blabla and the reminder date should be send to 3 known e-mail adresses.
Could this be done with an Excel Macro
Iím using Windows 7 32bits, Office 2010 with Outlook as mailing system.

Many thanks in advance.
Robert

Relevance 100%
Preferred Solution: Solved: Reminder date in Excel sends an e-mail

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

Answer: Solved: Reminder date in Excel sends an e-mail

16 more replies
Relevance 97.17%

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

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

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

how to set auto reminder when a due date is reached mentioned in excel EVEN WHEN EXCEL IS SAVED AND CLOSED?
 

More replies
Relevance 79.54%

hi !
I have a spread sheet of 100 of employees , i like every time the expiry date come for there id a notification email come to me , i attach the example excel sheet please help me with that, i am just learning VBA not very good in it i am using windows 8
 

More replies
Relevance 79.13%

I had made one excel to send automatic due date reminder mails via Groupwise (made with the help of your forums only).
The problem I am facing is as below:
[Also note, problem is not whether Groupwise or Outlook, problem is its generating multiple emails for each due item]

This is generating multiple emails and even the items not due are generating blank mails.
I would like to generate only one mail with different items different expiration dates.
After that group the identical addressees and compose one message with all the relevant data.

Right now its sending 3 mails as:

Mail 1: Please take notice of the following expiration date(s):
-Equipment A Job aaaaa expiration date : 19-Aug-12 -394 days.
-Equipment A Job aaaaa OVERDUE : 19-Aug-12 -394 days.
Sent at 17-Sep-13 11:11:04 AM

Mail 3: Please take notice of the following expiration date(s):
-Equipment C Job cccccc expiration date : 19-May-13 -121 days.
-Equipment C Job cccccc OVERDUE : 19-May-13 -121 days.
Sent at 17-Sep-13 11:11:04 AM

And the ones not due, going blank, i.e: the mail body (Mail 2) text reads:
"Please take notice of the following expiration date(s):
Sent at 17-Sep-13 11:11:04 AM"

What I wanted was a single mail with following in the mail body (Example):

" Please take notice of the following expiration date(s):
-Equipment A Job aaaaa expiration date : 19-Aug-12 -394 days.
-Equipment A Job aaaaa OVERDUE : 19-Aug-12 -394 days.
-Equipment C Job cccccc expiration date : 19-May-13 -121 days.... Read more

Answer:Request Help: Further study on Auto-mail for due date reminder

10 more replies
Relevance 78.72%

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

http://forums.techguy.org/business-applications/710581-solved-automatic-email-alerts-using-2.html
Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!
 

Answer:Automatic Email Reminder from Excel based on Date in Cell

Please do not post duplicate threads.
One thread per issue.
Continue replies for this issue in this thread: http://forums.techguy.org/business-applications/856705-automatic-email-excel-based-date.html
Thank you.

Closing thread.
 

1 more replies
Relevance 76.26%

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

Hi, Its my first post here. I need to send reminder mails from outlook based on excel
I searching for the VBA which according to my needs and I find in this Forum, but I have difficulty understanding. VBA that I adjust not functioning.

I attach the following

I need your expert solution

Any help appreciated and thank you.
 

More replies
Relevance 73.8%

GOOD DAY, NEED YOUR KIND ASSISTANCE TO THE FORMULA I MAY FORM TO SEND REMINDER E-MAIL TO MY CLIENTS IF ONE OF THE 3 COLUMNS CONTAINING DEPOSITS DUE DATE IS WITHIN 1DAY AND NOT YET BEING PAID.THANK YOU

Answer:Excel 2013 auto sending of reminder e-mail

This cannot be achieved with a formula you will need a macro. The requirement is fairly straight forward but we need some more information to be able to produce this. How is your workbook laid out? we need to know1) which cell has the dates in it2) which cell has the email addresses to which you want the email to be sent3) what the email body and subject will be Also please note writing it caps is considered shouting, please don't type in caps.

3 more replies
Relevance 72.98%

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

Hi

I want access to remind me 7 days prior to a set date that expires. Example: In my database I have a date that the trainees has completed training and this training is valid for a period of one(1) year. 7 days before this one year period expires I would like acess to give me a notification of all the candiates who is due for training in 7 days time....Is this possible?

Koosthuizen
 

Answer:Solved: Expiring Date Reminder/notification

16 more replies
Relevance 67.65%

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

Answer:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 67.65%

I am a Vista Home Premium user. I use Microsoft Outlook 2007 and Incredimail as my mail servers. It does not appear that this issue is specific to the software.

When I click on SEND, sometimes it goes through quite handily. Other times, it stops at CONNECTING, then I get the error message: "The outgoing mail server 'outgoing.verison.net' does not respond, please try again later. If this problem persist, contact your Internet Service Provider.' Details: Socket Error: 10060, Port 110, Protocol: POP.A connection attempt failed because the connected part did not properly respond after a period of time, or established connection failed because connected host has failed to respond."

Similarly, when receiving mail. I click on Send/Receive, I sometimes get the mail as usual, no problem. Othertimes, there is a delay with no authentication window, and I get the prompt:
"The incoming mail server 'incoming.verizon.net' does not respond, please try again later. If this problem persist, contact your Internet Server Provider.' Details: Socket Error: 10060, Port 110, Protocol: POP.A connection attempt failed because the connected part did not properly respond after a period of time, or established connection failed because connected host has failed to respond."

The weird thing is that this does not happen every time. Something like every other time!

Please, can someone tell me what is going on??
 

Answer:Solved: E-Mail Sends and Receives Erratically

9 more replies
Relevance 66.83%

I have 2 PCs with the same email address. We have had this set up for several years. Today, on this PC, Outlook Express 6 is sending me duplicate messages. I tried the Comcast chat option and got nowhere.

When I send myself a test message, this is the error : The message could not be opened from out Outbox folder. Account: 'mail.comcast.net', Server: 'smtp.comcast.net', Protocol: SMTP, Port: 465, Secure(SSL): Yes, Error Number: 0x800420C8

Eventually, the test message comes through. The other PC works normally.
The settings are : Outgoing SMTP 465, the incoming POP3 995.
I am not at all very sharp on this, so any help would be appreciated.
John
 

Answer:Solved: Outlook Express sends double in mail

I went bak and checked the setings again and somehow I got set up with 2 email accounts for the same address.
 

1 more replies
Relevance 66.83%

Using Excel: I need to send one email reminder to participants three months after they sign up.
This is only done once.
I need to mark a field "sent" after they have been sent this reminder.
The next time I check for people needing a reminder those marked "sent" will not be sent another reminder, only those who have not been marked "sent".
Thanks for the help.
 

Answer:Solved: Excel send reminder and mark it was sent

Hi, welcome to the forum:
Code:

Option Explicit

Sub SendUpdate()
Call MailData("subject string", "status file updated", "[email protected]")
End Sub

Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String)
Dim eSubject As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)
With Itm
.Subject = mSubject
.to = Sendto
If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
.Save ' This property is used when you want to saves mail to the Concept folder
' .Display ' This property is used when you want to display before sending
' .Send ' This property is used if you want to send without verification
' .Attachments.Add (Filename) ' Must be complete path'and filename if you require an attachment to be included
End With
Set app = Nothing
Set Itm = Nothing
End Function

You will of course have to add your macro to send the mail and then enter a true or false in the sent column

A handy link to use ... http://www.rondebruin.nl/sendmail.htm
 

3 more replies
Relevance 66.83%

hi there,

I have a list of memberships that need to be renewed at different dates (eg. every two or three years depending on type of membership)

Is there anyway i can make it to automatically send an email to remind me or another person the day it's due to renew?

The list is on excel with expiration date in one of the columns
thanks
 

Answer:Solved: Set email reminder base on Excel

you have another thread here
http://forums.techguy.org/business-applications/1055728-set-email-reminder-based-excel.html
please do not duplicate posts - closing this one
 

1 more replies
Relevance 66.83%

For some reason there is a problem with this programme I have built (with help from Computerman) now I have transferred it to another excel file.

When I try to set a Callback Reminder (my terminology), once I have entered the details an error comes up for some reason. The programme has been tried and tested by itself and worked brilliantly but for some reason it doesn't now.

Please use password lfc1979 on the login screen
 

Answer:Solved: Excel outlook calendar reminder

9 more replies
Relevance 65.6%

Hi,

I've noticed some similar queries on here and wondered if you could help me with a task i'm trying to achieve.

The attached spreadsheet is based on one I found elsewhere on the internet, and when a cell in the B column has a value above 200 it generates an e-mail based on the data. I've shown it in working form at the top of the sheet, and underneath is a test set of data for what i'm trying (and failing!).

What i'd like to do is change it so that rather than MyLimit = 200 (As it seems to be listed in the Macro/sheet view), it's triggered by any cells in Column C with a date on or before the date the spreadsheet is opened. When this happens i'd like it to say 'Sent' or 'Not Sent' as it does at the top of the page, but in Column D instead (I couldn't work out how to transfer it.

Lastly, when the e-mail is triggered it currently says:

"Hi Tom
The following client has now entered their expiration as of the stated date : 220
Regards,
Jake"

Whearas i'd like the expiry date from Column C placed in there, as well as the Client name just after "The following client, "

I've tried to edit it but so far had no luck, apologies for the lengthy message but hopefully these problems are quite easy to fix, any help greatly appreciated!

Thanks,

Jake

p.s. The next step is to work out how to get this spreadsheet to open automatically every day but I think that's a task for a rainy day... Read more

Answer:Excel - E-mail automatically sent depending on date?

6 more replies
Relevance 65.19%

Hi

I use windows 10, Excel 2016-32 bit (part of the MS office 365 kit).

I have an employee database made in excel, it has the date of birth of an employee,
based on the date i would like Excel to pic a random pic from a collections of pic stored in a second sheet of the same workbook, and email it as a HAPPY BIRTHDAY message to the email address of the employee and mark a copy to the organisation.

I use OUTLOOK 2016 ( part of the MS office 365 kit)

I am not a software developer and know not anything about coding.
appreciate all the help from this forum.. and thanks in advance for every feedback received.

Regards
sajumd
 

More replies
Relevance 65.19%

Dear Friends,

I'm in a serious need of updating the respective teams with reminders on due dates of statutory compliance requirements. I've developed an excel workbook having three sheets namely Permissions, Returns and Records. All the three sheets are having due dates. (Workbook is attached for your solution please)

I need your expert solution in sending an e- mail message as a reminder to the person concerned before 15 days of due date with an optional text of my choice.

Its not possible for me to open neither the excel sheet nor the ms outlook application on a time basis coz my profile mostly deals with Industrial Relations aspects. Hence I've to be in the field 12/7 during the shift period and on call 24/7.

Awaiting early reverts.

Regards,

SNU.
 

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%

Dear All,

I am creating a tracker file for Purchase Orders in which end date in column 'J' is very important.
Column 'J' has following conditional formatting
1. If cell date is in between todays date + 14 days --- YELLOW
2. cell date <= today's date ------------------------- RED
3. cell valus > today + 14 days ---------------------- GREEN

So now when the cell turn Yellow I want the excel should send the mail to the concerned person whose mail id will be mentioned in same row of column 'G'

One more requirement there should be two mails. Reminder 1 & Reminder 2.

I use officer 2010 & mail is outlook 2010 & OS is windows 7.
This file will be on share point. This file may not be opened everyday.

Please reply with the procedure as I am not a programming/ IT person... I may not understand all terms.. please try to simplify the response.
Thanks in advance for all the help.
 

Answer:Auto mail from Excel to Outlook based on date in cell

Hi there, welcome to the board!

You'd want a location to mark when an email was sent. I'm assuming you want a worksheet change event for this, which will basically always run when a cell on this specific worksheet is changed. There are other events you could use to fire it off, like the calculate event. You could, if you wanted to, assign this functionality to a button, but then it wouldn't be automatic.

The below code does what you ask. It goes in the worksheet module of the worksheet your data is on. To get to it, right click the sheet tab name and select 'View Code', then paste this there.

EDIT: The locations to mark when an email was sent (btw) were columns K and L, as you'll see them set as constants at the top of the code. You can change those letters to any column you want to house it in. It just puts the current system date in those cells, and that will be checked when the values in column J are checked. If nothing is there it assumes an email hasn't been generated yet and will do so. But if it has a value - any value, it will ignore that row.

Also, I assumed a 'yellow' highlighted value was your first reminder, and a 'red' highlighted value was your second reminder. It uses this as text in the subject and body of the email.
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Const ReminderOne As String = "K" 'column letter
Const ReminderTwo As S... Read more

1 more replies
Relevance 61.09%

Hi folks,
I need a little help how to have reminder date from Excel xls into Outlook calendar.
It might be simple but our office IT guys couldn&#8217;t figure out so I need some help... I created a sample xls file with proper columns &#8211; attached. I am not a programmer so I am trying to figure out how to have automatic reminders 3 days before the &#8220;Start Date&#8221; which will be different for each item and there will be thousands of items on this list.

I appreciate your help.
Thanks

PS I forgot to attach my xls and do not see a way to edit and attached so I may have to attach it in a reply...
or you can download from here http://www.allreversephonelookup.com/excel/info.xls

 

More replies
Relevance 61.09%

Hi....
Anyone can help me to advice how do i write VBA for auto email reminder for due dates.
I have tried one from internet guidance but it doesn't work.

i want the email reminder to a list of IDs before 60days and it's much helpful if the program can send the email without opening the excel sheet.

please help.
i have attaced the file.

thank you so much.
 

More replies
Relevance 60.68%

I'm using Vista Windows Mail and everything has been fine since I got this computer new 3 years ago or so. When sending any email (text only or with an attachment), the sending screen pops up and the little green bar slides across showing the progress as normal every time. When done, the email appears in the sent box showing a successful send. According to Windows Mail, all messages send OK. However, the only emails that arrive at the address destinations are messages with attachments or inserts of 195kbs and over. I've tried numerous test messages to myself and friends with text only and with attachment/insert files in size of 50kbs to over 200kbs and the result is always the same. The mail containing files over 195kbs are the only ones that come through to any email address.

I'm receiving all email (text only & attachments) from outside addresses as normal (at least I hope I am!). There are no email error messages whatsoever. There are no secondary email scans of outgoing mail by antivirus software. I've made no recent computer software/hardware changes. I've done system scans and there are no viruses or malware apparent. I've run a computer scan for errors with auto-fix and the system has been defragmented.

I don't want to put an attachment on every email I send, especially the business related ones. Has anybody else encountered this problem? Any info would be greatly appreciated. Thanks!

Answer:Windows Mail Sends OK But Only Mail With Certain Attachments Arrive

Hello, windows mail can have any email provider as default email service , who is the email service you use as default windows mail ?

10 more replies
Relevance 60.68%

Subject: Yahoo E-mail Sends Spam Mail without me knowing it.

I have an Yahoo Account that a friend told me that it was sending spam mail to him and it was a suprise to me because I did not send spam mail to that person. What is going on and how do I cure this problem? I use a couple of computers. One had Advast free on it and the other has AVG free on it. The other day AVG expired and it took it off of one computer. I try to put Advast on it and it would not allow me to do that.
What should I do to fix this problem.

What I have done so far.
1. Ran Malware bytes & found 2 infections on quick scan.
I have not done a full scan.
2. Change password and made a second e mail address to the account
and set it to primary.
3. I still need more advice on this subject.
Thank you,
 

More replies
Relevance 60.27%

hrhsherry writes on 9-30-03 about a page that comes up which has on the top bar "Reminder: with the correct day and day." The page has a heading "This page cannot be displayed". There is no 'X' in the upper right hand corner and the page cannot be resized. I have the same problem,which occurred about the same time as that of hrhsherry. I know I can make it go away by going to the Taskmanager (Windows XP) and ending the task. But, the screen pops up again over and over.

dvk01 (derek) said to hrhsherry to download HijackThis! and that he would look at the startup log on this forum. He's indicated to me to post MY startup log from HijackThis! on this security forum so that he or someone else can look at it and give me advice along the lines given to hrhsherry. So, here it is. As to the advice given to hrhsherry, I've already downloaded AdAware 6.181 and I have Norton Anti-Virus 8.00 on my system already. I'll be downloading Spybot, to follow through, once I get a reading on this startup log. I'd ALSO like to know how to prevent this nuisance "reminder" from getting into my system again

dvk01, let me know if you can help.

kamull01
********************
Logfile of HijackThis v1.97.3
Scan saved at 10:16:10 PM, on 10/28/2003
Platform: Windows XP SP1 (WinNT 5.01.2600)
MSIE: Internet Explorer v6.00 SP1 (6.00.2800.1106)

Running processes:
C:\WINDOWS\System32\smss.exe
C:\WINDOWS\system32\winlogon.exe
C:\WINDOWS\system32\serv... Read more

Answer:HELP-hijackthis.log for The page cannot be displayed & Reminder w/ day's day and date

Could someone look at my hijackthis log to see what's causing a message to pop up all the time? The screen for the message looks like a standard Microsoft Windows Internet Explorer message screen. It says "This page cannot be displayed" and the title bar has the words "Reminder" and the day's date and day. However, there is no X to close the window and the window cannot be resized. I can get rid of the message by going to taskmanager and ending the process, but it pops right up again.

Nobody's answered yet, but I think there's someone out there who could help.

kamull01
 

3 more replies
Relevance 60.27%

Have started to get a small popup window (approx 3" x 3") in center of screen every half hour that has heading Reminder S, September 29, 2007 Saturday 4:30:00 PM, with starndard internet error message:This program cannot display the webpageMost likely causes: - You are not connected to the internet. - The website is ..... etc. O More informationWindow has no close, full screen or send to task bar buttons. The reference tab on the task bar has no right or left click functions. The only way to close the window is to use End Task in the Windows Task Manager. When I close it, another popup appears at the next half hour interval (new time).Have looked thru all the IE 7.0 options, tabs, etc. and see nothing to indicate any type of time reminder or scheduled popup.What is this?

Answer:Ie 7 Reminder (date & Time) Window Popup

Hello SCP,The following all seem to point to HP and some "Easy Internet Sign-Up" feature as the culprit:http://h10025.www1.hp.com/ewfrf/wc/generic...=en&lang=enhttp://www.softwaretipsandtricks.com/forum...-up-window.htmlhttp://www.pcreview.co.uk/forums/thread-562504.phphttp://www.tweaknews.net/forum/viewtopic.php?p=5825

2 more replies
Relevance 59.86%

Hello Friends,I am practical student. I in process to complete my mini project during internship. I need to make a auto reminder in outlook by using excel when due date arrived. It is one of challenging task because I am student in Major business and minor in IT.There are a lot of coding I'm try.But still not success.I hope can learn more. I also love to learn new thing.I hope somebody can help me to complete this task before I finish my practical training. Thanks and regards,Zulaika

Answer:How to send auto reminder in outlook if due date arrived?

Can you clarify something pleaseAre you looking to have a number of dates in Excel, which when reached/passed Excel is to create an email via Outllook and send it to someone?Can you provide an example of how your workbook is laid out

2 more replies
Relevance 59.86%

Hello Friends,I am practical student. I in process to complete my mini project during internship. I need to make a auto reminder in outlook by using excel when due date arrived. It is one of challenging task because I am student in Major business and minor in IT.There are a lot of coding I'm try.But still not success.I hope can learn more. I also love to learn new thing.I hope somebody can help me to complete this task before I finish my practical training. Thanks and regards,Zulaika

More replies
Relevance 59.86%

Hi, i am new to this forum. Can anyone tell me if i can have a date reminder system for my database in Access? Basically its a licence database and each year a form has to be isgned by each licence holder. is there a way the program can remind me for each name when the form needs to be resigned each year on a particular date?many thanks

Answer:Microsoft Access Data base date reminder?

There doesn't seem to be that facility in Access.Just as easy to use Outlook I would think.

3 more replies
Relevance 59.86%

Hello,

I was able to successfully apply Hans' module and have the spreadsheet send an email when it was opened if something was out of the calibration date.

My question is now if it is possible to have it send 2 weeks in advance, and then again in 1 week later. I know that because the cell will be occupied that it was initially sent, it will not read it again for another send. Is there a way to automatically clear a column so that it just sends an email every time its opened within that range?

Finally, where do I add the column for a CC email?
Code:
Option Explicit

Public 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
Dim ws As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set ws = Sheets("Master Equipment LIST")
Sheets(1).Select
lRow = WorksheetFunction.Max(3, ws.Cells(Rows.Count, "K").End(xlUp).Row)
If ws.Cells(lRow, "K").Value = "" Then Exit Sub
For i = 2 To lRow
toDate = Replace(Cells(i, "K"), ".", "/")
Debug.Print WorksheetFunction.Days360(Date, ws.Cells(i, "K").Value)
If WorksheetFunction.Days360(Date, ws.Cells(i, "K").Value) <= 14 And Len(Trim(ws.Cells(i, "M").Value)) = 0 Then
' If Left(Cells(i, 5), 4) <> "Mail" And toDate - Date <= 30 Then
t... Read more

More replies
Relevance 59.45%

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

Anyone can help me? Thanks.
(btw, is it possible in the first place?)
 

Answer:[SOLVED] Day & Date in Excel 97 SR2

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

2 more replies
Relevance 59.04%

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

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

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

Here is the formula:

YEAR(NOW())-YEAR(F6)

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

Anyone have any thoughts?

Regards,

TBaker14

 

Answer:Solved: Excel Date formulas

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

3 more replies
Relevance 59.04%

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

here is an example

4/20/1991 to 04201991

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

Thank You,
Andy
 

Answer:Solved: EXCEL Date formats

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

3 more replies
Relevance 59.04%

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

More replies
Relevance 59.04%

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

Any help with this would be greatly appreciated.
 

Answer:Solved: Excel Enter Date

13 more replies
Relevance 59.04%

Hi guys,

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

What I want to do is this:

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

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

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

Thanks,

Peaker.
 

Answer:Solved: Help. Need help with date triggers in Excel

8 more replies
Relevance 59.04%

Good afternoon all,

Long time no seek help!! (cos I learnt so much last time )
I have a worksheet containing a list of stock i.e. Col A = Date; Col B = Product Description; Col C = Amount of stock

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

Stock older than 18 months
Stock held in store for 12 - 18 months
Stock held in store for 6 - 12 months
Stock held in store 0 - 6

Does this make sense - Is this possible?

Thanks if you are reading this !

Moll
 

Answer:Solved: Excel Date range

16 more replies
Relevance 59.04%

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

Anyone have any ideas?

Thanks
[email protected]
 

Answer:(Solved) Excel Date format

Hiya

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

eddie
 

3 more replies
Relevance 59.04%

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

Column A
09/20/2012
09/21/2012
10/20/2012
02/20/2012

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

10/20/2012
09/20/2012
09/21/2012
02/20/2012

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

Help!
 

Answer:Solved: Excel Sort by Date

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

2 more replies
Relevance 59.04%

Hello,

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

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

Thanks in advance.
 

Answer:Solved: Excel auto date

Hi there, welcome to the board!

If you have "Date" in A1, and your start date entered manually into A2, you can put this in A3 and copy down...
Code:
=IF(WEEKDAY(A2)=6,A2+2,IF(WEEKDAY(A2)=7,A2+1,A2))+1
HTH
 

2 more replies
Relevance 59.04%

I have the following code
Code:

Dim EndDate As Date

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

If ws.Range("K4") <> "" Then
'Do Nothing
[B]ElseIf Date = EndDate Then
ws.Range("K4") = EndDate[/B]
Else
ws.Range("K4") = Date + (7 - Weekday(Date, 1))
End If
 

Answer:Solved: Excel VBA Enter Date

13 more replies
Relevance 58.22%

the answer to this might of been found with a search but hey i'm lazy today and wanted to see who the excel masters are

using excel 2007, cell a1 contains an array formula (CSE) like so - {=MAX(LEN(A2:A470000))}
cells a2 through a470000 contain dates such as
12/12/2011
etc, you get the idea, i need the formula or something else entirely to return 10 not 5

thanks in advance
 

Answer:Solved: excel length of date field

i'll answer if myself thanks to another web site, Mike these guys are slackin' 'round here it looks like

=MAX(LEN(TEXT(A2:A470000,"m/d/yyyy")))

exit celll with ctrl, shift, enter
 

1 more replies
Relevance 58.22%

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

Answer:Solved: excel unwanted date columns

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

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

see attached
 

2 more replies
Relevance 58.22%

Sorry for the newbie question, but here goes:

I am using Excel 2007 and I have a spreadsheet that uses 2 dates -- the buy and sell date. I have a column that calculates how long we held the product. I use an IF statement to put a zero in the column if the product hasn't been sold yet which looks something like this:

IF(ISBLANK(buy_date),0,(sell_date - buy_date))

Now I want to summarize the "time-to-sale". I want to count all "0" sales (unsold), all sales between 1-30 days, 31-60, 61-90, through the whole year. The problem is, I can't use a macro because this spreadsheet needs to be "macro-free".

Suggestions?

Thanks,
gpence
 

Answer:Solved: Excel formula -- by date summary

7 more replies
Relevance 58.22%

I'm trying to have a header display the last updated date on a spreadsheet. I know how to display the current date, but that might be different from the last update date, which would basically be the last date the spreadsheet was saved. Is there a way to do this? Thanks
 

Answer:Solved: excel: displaying last save date

9 more replies
Relevance 58.22%

I'm looking for a formula for excel, but can't seem to fine tune one, or find an existing one.

The spreadsheet is for applications. If initial contact happens on say 7/20/2010 and the applicant has 5 days in which to respond, for example. I want a column that posts the due date for follow up automatically, and then also a column that says either NO or YES in regards to Follow Up Needed that also updates automatically based on Date Of Follow Up having passed.

I hope this makes sense. I'm going to attempt to put an example below.

Application Date Applicant Name Date Of Follow Up Follow Up Needed
7/20/2010 John Smith 7/26/2010 YES
7/25/2010 Jane Smith 7/31/2010 NO

EDIT: Well, it won't maintain the spacing, but that should give you an idea of what I mean.
 

Answer:Solved: Excel Formula if a date is exceeded

11 more replies
Relevance 58.22%

I have a tab delimited .txt file. One of the fields has 5-2056 in it. When I open the file in Excel, it gets changed to May-56. How can I stop this? I can't edit the data, and I can't format the cell (it's not an actual cell/spreadsheet).
 

Answer:Solved: Excel, stop changing to date

If you want the 5-2056 kept as is, then do the following.
Select all the cells with data in
Click on Data, Text to Columns
Delimited, NEXT
Select the delimiters you want to apply e.g.TABS, NEXT
The 5-2056 should now be shown in its own columnand the heading should say general. If so just click on Finish
It should now be in its own columns and the 5-2056 will appear just as that.

hope that works foryou.

Come back if you need more clarity.
 

3 more replies
Relevance 58.22%

In EXCEL (Office 2002) Is there a way to have a header that is auto matically updated to show the date the file was last edited(changed) ? . It should do this each time the file is edited(changed). Then when the file (.XLS) is emailed to someone and they print it, The last "edit(changed)" date will show .Currently the local date the file is printed is showing.
Manually editing the header is forgotten at busy times.
 

Answer:Solved: Show last edit date in Excel ?

7 more replies
Relevance 58.22%

Excel gurus,

Back again with another difficulty...

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

A1
06/25/2001 15:27 1.4

With the formula =LEFT(A1,16) and =Right(A1,4) I am able to extract the Date and time from the variable from the cell as follows.
A1 II A2 II A3
06/25/2001 15:27 1.4 II 06/25/2001 15:27 II 1.4

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

A1 II A2 II A3 II A4
06/25/2001 15:27 1.4 II 06/25/2001 15:27 II 37067.64 II 1.4

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

Answer:Solved: Excel XP Date time difficulty

9 more replies
Relevance 58.22%

I have a macro that I want the user to input a date such as 12/08/08 and then conditional format column O for any cells containing date greater than the user's input date. The only thing it highlights is the header. I am not sure how to get the formula= MyDate to function correctly. Any help would be appreciated.
Code:

Sub TestUserInput()
'
' Macro1 Macro
'
'
Dim MyDate As Date
Columns("O:O").Select
MyDate = Application.InputBox("Enter a Date")
' MyDate = DateValue(MyDate)
MsgBox "The name you entered was " & MyDate

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:="& MyDate"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
 

Answer:Solved: Excel 2007 Date Variable

7 more replies
Relevance 58.22%

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

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

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

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

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

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

thanks
Ray
 

Answer:Solved: STRANGE Excel date issue

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

Start>Settings>Control Panel>Language and Regional Settings

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

Kevin
 

3 more replies
Relevance 58.22%

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

Start End Round
04/12/2009 05/15/2009 1

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

Task Start Date End Date Round
Final Material Review 31-Aug-09 31-Oct-09 0

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

Answer:Solved: VLookup in Excel Using Date Ranges

10 more replies
Relevance 58.22%

Hi everyone,

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

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

I have attempted conditional formatting and am getting nowhere.

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

Answer:Solved: Date Alert Excel 2003

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

should do it
this will test for 31 days less
in one of the conditional format rules put in 3 rules
=if(S2="Y", true, false) make green

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

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

see attached spreadsheet - tests Column C2
 

3 more replies
Relevance 58.22%

Hi everyone,

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

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

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

I have attempted conditional formatting and am getting nowhere.

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

More replies
Relevance 58.22%

Hi

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

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

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

Any ideas?

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

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by Barrie Davidson
If Target.Column = 1 Then
Target.Offset(0, 1).Value = Now()
End If
End Sub
Thanks
 

Answer:Solved: Excel - Auto date when modified

Hi, this appears to do it.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column > 0 And Target.Column < 11 Then
Target.Offset(0, 11 - Target.Column).Value = Now()
End If
End Sub
 

3 more replies
Relevance 58.22%

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

More replies
Relevance 58.22%

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

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

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

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

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

Thanks a bunch.
 

Answer:Solved: Date format - Excel 2007

Hi Traci.

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

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

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

3 more replies
Relevance 58.22%

Tech Support Guy System Info Utility version 1.0.0.1
OS Version: Microsoft Windows 7 Professional , Service Pack 1, 32 bit
Processor: Intel(R) Core(TM) i5 CPU M 520 @ 2.40GHz, x64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 2993 Mb
Graphics Card: Intel(R) HD Graphics, 1272 Mb
Hard Drives: C: Total - 234274 MB, Free - 193142 MB; E: Total - 2048 MB, Free - 2010 MB;
Motherboard: Hewlett-Packard, 1722, KBC Version 29.36, CND037022T
Antivirus: McAfee VirusScan Enterprise, Updated and Enabled

I would like to add a formula that would automatically calculate a date field from one cell and add three years. See example attached, Thanks in advanced
 

Answer:Solved: Calculating Date Fields in Excel

7 more replies
Relevance 58.22%

Hi I realise that this is a common thread but I couldn't find a way to make it work for me in particular.

I have some cells that i want to add dates to in the future. they will contain a date that i want to add 14 days to, so that i know when to delete something two weeks later.

the formula i have used is =DATE(YEAR(Vicki!C16),MONTH(Vicki!C16),DAY(Vicki!C16)+14)

but because that formula is operating on a cell (in a seperate sheet) without a date in it currently, the cell displays 14/01/1900

I would like the cell to display as empty/clear until there is a date in the cell I want to add the 14 days too.

Hope that make sense
Any help gratefully received
 

Answer:Solved: Excel displaying a date when i dont want it to

Hi tiggly, welcome to TSG.

You need to add an If statement so that Excel will check whether the cell is blank or not. Try something like this:

=IF(Vicki!C16="","",DATE(YEAR(Vicki!C16),MONTH(Vicki!C16),DAY(Vicki!C16)+14))

Hope that helps.
 

3 more replies
Relevance 58.22%

Hello:
I am in college , have a professor that thinks I understand excel , and have been sitting here for about 5 hours trying to do this and even the simplest things are not working for me.

Below are the directions given, I have used the AND function and filled it out but it does not give me what I need. I need it to look like this with the word valid beside the date, under validity. But it keeps telling me false or invalid when I fill out the AND function guide.

There is more directions listed for other things that I have to do and Have not been able to get correct either. I did get correct the cells that only had to list dates. But I have not gotten correct much more than that.

I am rather frustrated and really need any help anyone can offer.

User Input: employee data (cell a1)
Field Value Validity (cells a2,b2,c2)
Employee start date: 1/1/2009 valid (cells a3,b3,c3)
Employee end date: 12/31/2009 valid (cells a4,b4,c4)
Annual paid vacation days: 10 valid (cells a5,b5,c5)
Hourly pay: $10.00 valid (cells a6,b6,c6)
Hours per day: 8 valid (cells a7,b7,c7)
DIRECTIONS:

Employee start date: The date (B3) is valid if it is:
o on, or after, the &#8220;year begin&#8221; date in F3, and
o on, or before, the &#8220;year end&#8221; date in F4, and
o on, or before, the employee end date in B4

&#8226; Employee end date:
The date (B4) is valid if it is:
o on, or after, the &#8220;year begin&#8221; date in F3, and
o on, or before, the &#822... Read more

Answer:Solved: Excel: valid new employee date using AND

Well a couple of things. We won't do your homework for you. You must do the work. So no solutions will be provided to you. What I will do though, is try to give you some insight here so that you may come up with a solution on your own.

The AND() function is not helpful in validating dates, in fact that is a completely false statement. It is a logical function, which means it will have a boolean return (either TRUE or FALSE). The only difference between that and an OR() function is that all arguments inside the function must return TRUE for the function to return a TRUE result. With the OR() function, any one single item in the function must be TRUE for the function to return TRUE, whether that is all of them or just one, but if none of them are TRUE then the function returns FALSE.

Here are some examples...

=AND(2>1,4>8) would return FALSE
=AND(4>2,4<8) would return TRUE
=OR(1=2,3=4) would return FALSE
=OR(1=2,3=3) would return TRUE

So then the next part would be to ensure that your arguments return a boolean return. If you're looking at dates, this would return a boolean return...

=TODAY()=A2

That says today's date is equal to the value of A2. That is TRUE or it is FALSE, dependent on what value you have in A2. Make sense?

Another little tidbit of information when dealing with dates in Excel is that one whole day is expressed as one whole number. So half a day (12 hours) would be expressed as 0.5 in decimal form. Thus, to calculate hours, if... Read more

1 more replies
Relevance 58.22%

I need to use formulas to caluculate a given number of workdays before a given date.

For example:
A project is due on August 31

I need to figure
2 workdays before that date
7 workdays before that date
13 workdays before that date
26 workdays before that date

Can anyone help?

Thanks
 

Answer:Solved: Excel 2003, Calculating a date

7 more replies
Relevance 58.22%

Hi,

for each client I have I am calculating a date that is three years in the future minus one day.
(i.e. 02/01/2011 gives an output of 01/01/2014)

I want the formula to check the client's date of birth in another cell and calculate a different date if the child is under 18. this date should be 21 years from the date of birth minus one day.

so excel is checking the date of birth of my client and performing a different calculation dependant on their age.

Please see attachment for an example.

any help would be greatly appreciated.
Regards,
Dan
 

Answer:Solved: Conditional Future Date in Excel

If you google for it yolu will find a lot of code to calcuate age.
I added the function AGE() to your file and included it your formual.
The sheet must be macro enabled to run.
 

2 more replies
Relevance 58.22%

Now I need to calculate a date from a given date using 13 workdays prior if the given date is a Monday and 14 workdays prior if the given date is a Wed.

Is that possible?

Thanks
 

Answer:Solved: Excel 2003, Calculating a date using ifs

This will give you the dates for those and a blank for any other days:
Assuming your date is in column A
=IF(TEXT(A1,"dddd")="Wednesday", A1-14,IF(TEXT(A1,"dddd")="Monday",A1-13,""))
 

1 more replies
Relevance 58.22%

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

Answer:Solved: Excel 2000 date merge

11 more replies
Relevance 58.22%

Down Under we show our dates as dd/mm/yy. I have a user who types in 3/4/00 and the system automatically reverses the date to 4/3/00. I have checked the properties on the regional settings, and the date options in Excel are also correct. I have tried, in Tools/Options/Calculations to change the date format to 1904, but the system wont accept it.

Any ideas for resolution appreciated.

Ms. Kumara
 

Answer:(Solved) Excel date format wrong

In my 4 years in customer services for a well known computer company, I have never seen that before.

Microsoft Excel does not control the dates nor the settings, ie currency etc. Which version are you using?

Try using another Microsoft program like Word to see if you have the same problem, if not, then you would need to speak to Microsoft in New Zealand.

Also, double check to see under Regional Settings in Control Panel, in regional settings tab, make sure the setting is English (New Zealand), and press apply (if it is not greyed out. If it asks you to restart, press no and go to the Currency and Date tabs to make sure that the setings are for New Zealand.

If that fails, then you can *force* the machine to your own custom settings, by changing the values.

If all else fails, then it could be Windows itself.

Please email me to let me knowhow you got on.

James
 

2 more replies
Relevance 58.22%

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

Answer:Solved: Excel 2007 date stamp

7 more replies
Relevance 58.22%

Hi everyone

I hope all are fine and reading my message in the best of their health.. I am stuck with a job to do on various dates where i have to email those candidates whome progress reports are due, which due dates are mentioned in the sheet.

Can some body please help me to come up with a excel automatic mail solution that an email should be generated via outlook without opening the file or even if needed opening the file. The email should contain

Dear Student

Your progress report / proposal defense is due on (due date),

regards,
this email should be generated and sent before one month and one copy on the date on which it is due.. the cc list also needed to be grabbed from somewhere, where cc of this email should go.

I shall be very thankful if somebody can look the attached file and help
 

Answer:Solved: Excel Automatic Email on due date

16 more replies
Relevance 58.22%

I have a cell with a date value of "4:00:00 PM" and I'd like to be able to hit the + key and increment it by 1 hour (so it would become "5:00:00 PM", then "6:00:00 PM", etc.). Is that possible?

P.S. I'm using Excel 2003.
 

Answer:Solved: Excel: Can I increase date/time with +/- key

9 more replies
Relevance 58.22%

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

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

Any ideas?

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

Answer:Solved: Inserting date with Excel macros

See the code below. If you want to also include the time in addition to the date you can use replace the word Date with the word Now
Code:
Range("A2").Value = Date
or
Code:
ActiveCell.Value = Date
Regards,
Rollin
 

3 more replies
Relevance 58.22%

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

Answer:Solved: Excel 2002 Date Format

6 more replies
Relevance 58.22%

I'm using ms office 2003 excel. I have a column date that is in a format of 20100410 and I would like to convert it to April 10, 2010. Any ideas?
 

Answer:Solved: change date format in excel

If the column is actually a date (not text) then highlight the column and go to Format, Cells. Select the Number tab and then click on the Date format. A list of formatting options will appear and you should find what you want.
If it doesn't work, do the same thing and select Number. If you actually have dates typed in, then 20100410 would show as 40278.00 - if it is text, it will show as 20100410.00.
 

3 more replies
Relevance 58.22%

hi, the goal is to calculate the difference between the 'year' portion of 2 date entries in different date formats; example:
if cell 1 contains:
12/26/2000
and cell 2 contains:
31-Dec-11

what is the formula to get the result?
 

Answer:Solved: excel - date difference in years

6 more replies
Relevance 58.22%

Hi

I hope somebody can help.

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

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

Thanks

Dave
 

Answer:Solved: Excel 2010 - date Calculations

use networkdays()
or
workdays()

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

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

3 more replies
Relevance 57.81%

How do you arrange e-mails in descending date order in Windows Mail?

Thanks
 

Answer:Solved: Windows Mail - Date Order

Select View --Sort By-- and check off Received and Descending.


 

3 more replies
Relevance 57.81%

Hello, I have created a macro that automatically generates a number of e-mails with a "due date" field. However, when the outlook e-mail is generated it displays the date due in mm/dd/yyyy. I am using a c.Offset (#,#).Value to pull in the date on my excel spreadsheet but even if I change the formatting on it it still pulls in with month first then day. Is there a way around this so it will display dd/mm/yyyy instead? Thanks for the help!
 

Answer:Solved: Date expressed as dd/mm/yyy in macro e-mail

Hi there,

Have you tried using the Format() function?...
Code:
Format(c.Offset (#,#).Value, "dd/mm/yyyy")
HTH
 

3 more replies
Relevance 57.81%

I am trying to build an excel sheet to tell me my future manning levels, (for example, how many workers will I have 6 months from now). I attached (or at least tried to) a very small scale "example sheet" of what I am talking about. It basically will have one cell to put a start date in for which month to start gathering data. This is followed by the months following the start date going out a year. The data comes from a separate sheet and the data is based off of a column with dates that I am suppose to receive inbound and a column with dates of personnel departing. I am trying to take the month that I need the data for, count the personnel that should have arrived by that date and add it to those that have not departed by that date. Every formula that I have tried counts every cell to include the empty ones. This is the last formula that I tried =SUM(IF(Manning!$C$3:$C$14<=H3,1,0))+SUM(IF(Manning!$D$3:$D$14>=H3,1,0)). I have also tried COUNTIF formulas. How do I get it to count only the cells that have a date in it that falls with in the range? Sorry if it is confusing but hopefully the attached sheet will help explain. I am using an XP machine.
 

Answer:Solved: Using excel to count a specific date range

I put in a check to put in a ZERO if there is an empty cell.

=SUM(IF(Manning!$D$3:$D$14<=H3,IF(Manning!$D$3:$D$14="",0,1),0))+SUM(IF(Manning!$F$3:$F$14>=H3,IF(Manning!$F$3:$F$14="",0,1),0))
 

3 more replies
Relevance 57.81%

I have a personnel tracker that tracks required documents that need to be renewed every year. There are 4 of these and each has a seperate column for each date. They are J,L,T and U.

I would like excel to scan each of these columns every time the document is opened for any of those dates that are 60-65 days from expiring, I use Now()-300 to check this.

But if the cell is blank I want it ignored, which right now it does not. If it finds 1 or even 80 that are in that range I want 1 email generated to a list of management personnel.

Currently it generates how ever many are blank or expiring. I use outlook.

My data starts on row 3 and goes down to 331, the required and maximum number of people allowed on the contract.

If I can get just one email with a generic message I would be happy. But if I could I would like the info that is actually doing the triggering to be copied and pasted in the email to include the info in A, C and D.

Here is my current code, I would change .display to .send once I have it working. please help.

Private Sub Workbook_Open()
Dim Cell As Range
Dim DateRng As Range
Dim Msg As String
Dim olApp As Object
Dim olEmail As Object
Dim RngEnd As Range
Dim Wks As Worksheet

Set Wks = Worksheets("Work Site Info")

Set DateRng = Wks.Range("J3")
Set RngEnd = Wks.Range("J331")
Set DateRng = IIf(RngEnd.Row < DateRng.Row, DateRng, Wks.Range(DateRng, RngEnd))

'Change this to what you want.
Msg = "This is message is the bo... Read more

Answer:Solved: Automatic email from excel based on date

16 more replies
Relevance 57.81%

Does anyone know how to change the format of the [date] that is put into a footer?

Thanks!
 

Answer:Solved: Excel - Changing date format in footer

Hi slgraham22,

the only way is via a macro: http://support.microsoft.com/?kbid=213742

lol
Hew
 

3 more replies
Relevance 57.81%

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

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

Answer:Solved: Excel 2003 - COUNTIF() Date Comparison

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

Range should be a range of cells, your formula will at most return 1 if O73 < today.
If you want to count all the ones between O1 and O100 you'd put O1:O100 in the place of range.

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

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

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

2 more replies
Relevance 57.81%

Hi

I am trying to get a date value from one sheet to populate another sheet using the offset function.

Using:

Range(Selection, Selection.End(xlDown)).Offset(0, 3)= " (need to show date here from another sheet) "

Any help would be much appreciated..

Thanks
 

Answer:Solved: Using Offset in Excel VBA to Return date frm another cell

8 more replies
Relevance 57.81%

Hi,

Want a macro to sort data from (Column B,C,D,E) with whatever date mentioned in Cell A2.
Column B,C,D,E have different dates what I want is to sort those columns as per the date mentioned in cell A2.
Suppose if in cell A2 the date is 11/26/2009 then it should sort the data from the Column B,C,D,E with that particular date in a new sheet in that workbook.
And would like to assign that particular macro to a button

Please help
 

Answer:Solved: Sort today date with Macro in excel

16 more replies
Relevance 57.81%

Hi everyone, I was having some trouble with the format of the date in my excel document.

Basically what i want to do is type in for example todays date (Aug 18), and then when clicking below onto the next row, I would like to see the date come up above as (08/18/2005).

Instead the problem im getting is the date comes up as (08/18/2018)????

Ive tried right-clicking the entire column, where the date is being entered, and using "Format Cells" to set the appropriate date format. I have also verifed the date is set at 2005, so far nothing works.

I welcome any ideas/suggestions???

Thanx everyone
 

Answer:Solved: Trouble formatting date in excel spreadsheet

10 more replies
Relevance 57.81%

Is there a way to write a formula that adds down the column 1 day to the previous day, but when it hits a Saturday or a Sunday, it goes to Monday?
ie: (a1+1) but by the time it got to April 3 it would know to add 3 to get to Monday
Monday, March 30, 2009
Tuesday, March 31, 2009
Wednesday, April 01, 2009
Thursday, April 02, 2009
Friday, April 03, 2009
Saturday, April 04, 2009
Sunday, April 05, 2009
Monday, April 06, 2009
Tuesday, April 07, 2009

Thanks for any information you can provide.

BL
 

Answer:Solved: excel date formula - ignore weekends

try:

=a1+IF(WEEKDAY(a1)=6,3,1)

If WEEDAY of previous cell = 6 (=Friday) then add 3 else add 1
 

3 more replies
Relevance 56.99%

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

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

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

Any help would be greatly appreciated!

Found the answer I was looking for:

Assuming the first cell is A1,

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

Copy down, then copy the column and paste values.

You can also use

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

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

More replies
Relevance 56.99%

I'm suffering formula confusion...

In a simplified example, say I have an Excel file with data in columns A, B, C and D - Item, Position, Start and End.
These might be something like:


ABC__Pos1__20/09/2014 15:00__21/09/2014 09:00
DEF__Pos2__20/09/2014 23:00__21/09/2014 11:00
GHI__Pos1__21/09/2014 13:00__22/09/2014 06:00
JKL__Pos1__23/09/2014 17:00__24/09/2014 10:00
JKL__Pos2__23/09/2014 17:00__24/09/2014 10:00
MNO__Pos3__24/09/2014 09:00__24/09/2014 20:00
PQR__Pos1__24/09/2014 19:00__25/09/2014 05:00
Click to expand...
1) The first result I need is, if any of these time/dates overlap for different Items, to say "YES" in column E and if not, to say "NO" (or TRUE / FALSE or whatever and I'll do a find-replace later).
So I'd end up with:


ABC__Pos1__20/09/2014 15:00__21/09/2014 09:00__YES
DEF__Pos2__20/09/2014 23:00__21/09/2014 11:00__YES
GHI__Pos1__21/09/2014 13:00__22/09/2014 06:00__NO
JKL__Pos1__23/09/2014 17:00__24/09/2014 10:00__NO
JKL__Pos2__23/09/2014 17:00__24/09/2014 10:00__NO
JKL__Pos3__23/09/2014 17:00__24/09/2014 10:00__NO
MNO__Pos3__24/09/2014 09:00__24/09/2014 20:00__YES
PQR__Pos1__24/09/2014 19:00__25/09/2014 05:00__YES
PQR__Pos2__24/09/2014 19:00__25/09/2014 05:00__YES
Click to expand...

ABC's date range overlaps the date range of DEF, so both say YES.
GHI doesn't overlap anything, so it says NO.
JKL appears in 3 different positions during its stay, but it doesn't overlap with anything else, so these ... Read more

Answer:Solved: Excel If date/time ranges overlap, with condition

8 more replies
Relevance 56.99%

Hi there,
I came across this thread (now closed)
http://forums.techguy.org/business-applications/473412-solved-adding-date-time-last.html

Adding Date and Time Last Modified to footer of an Excel worksheet.

It adds the date to each worksheet.
Could someone please show me how to modify it to add the date to each worksheet ONLY IF that particular worksheet has changed?

Thank you in advance.
 

Answer:Solved: Adding Date and Time Last Modified to an Excel worksheet

Hi there,

Define "change". As in, any change whatsoever? And did you use the workbook before save event? Use a workbook event to get what you want, just use the workbook sheet change event (still in ThisWorkbook module), and be sure you change the activesheet reference (in your linked thread) to the sheet object name (in the parenthesis of the event, I believe it is "Sh"). That should pretty much be it. If you need more help, post your code and tell us exactly what you are looking to do in a little greater detail.

HTH
 

3 more replies
Relevance 56.99%

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

Is there a function that does this?

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

TIA, TTFN
 

Answer:Solved: Excel 2003 - add worksheet date last modified to a cell

6 more replies