Computer Support Forum

how do I create Excel Email alert for expiring dates

Question: how do I create Excel Email alert for expiring dates

Hello, I have an excel document we use to track the "end" dates of an internet service we offer. What I am trying to do is automate the expiration date of the sheets in excel. When the expiration date for the service of a client is coming up, I would like to receive an email 3 days in advance letting me know it is about to expire. Any help would be great. thank you.

Relevance 100%
Preferred Solution: how do I create Excel Email alert for expiring 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.)

Answer: how do I create Excel Email alert for expiring dates

You are going to need a Macro to send yourself an E-Mail.Look here:http://www.rondebruin.nl/win/sectio...also, if you search the OFFICE forum with the keywords MACRO and EMAILyou should get several hits.MIKEhttp://www.skeptic.com/

8 more replies
Relevance 86.1%

Does anyone know a way to create an email alias? I sometimes need to email data to people without enabling them to contact me (they're supposed to go through a help desk). I've looked at services like 10minute mail, but they don't enable you to create and send mail, only receive it. Any ideas?

Answer:How to create an email alias or expiring address?

Go through a help desk? Can I assume there is a web site, with a domain name in the mix somewhere?

I would use the email services connected to the web site or domain name to create a generic email account, then configure it with an "auto response" that responds to any email sent to it with a message to contact the help desk. You can then use the email to send and do not have to be concerned if the recipient attempts to respond to the email instead of contacting the help desk.

However if you only have access to "free" email like google mail, yahoo mail, etc, I do not think you have this option. It might be time to have your own domain name and use the associated email account?

Cheers!
Robert

7 more replies
Relevance 77.08%

I have this for my "current" column =IF(TODAY()-C2<30,H2," ") and this for my 90+ column =IF(TODAY()-C2>90,H2," ")How do I change this formula to get 30+ and 60+ columns?

Answer:Excel If Then with dates & amounts to create aging

What is it your doing?Your "current" formula looks for Less Than 30 dayswhile your other looks for Greater Than 90 daysA bit more of an explanation of what it is your trying to accomplish would help.How do I change this formula to get 30+ and 60+ columns?=IF(TODAY()-C2>30,H2," ") should get you greater than 30 =IF(TODAY()-C2>60,H2," ") should get you greater than 60But be aware that the 30 Days is within the 60 days,so when it's 60 days both will be true.And Excel questions are best asked in the Office Software forum.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

3 more replies
Relevance 72.57%

I want to have an email sent with the topic of the training and the name of the person. this is according to the date but since it has a lot of trainings and dates it works with columns instead of rows.

It has two due dates one for a yearly email reminder and one for every 3 years.
 

More replies
Relevance 71.75%

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

Greetings all,
I am a novice at best on using excel, but through some searching and experimentation I have managed to get to a roadblock I cannot pass. I am working on setting up a workbook that emails at specific dates for reminders to people in my department. I believed I had it working however I have run into two issues.
One is a run type error 13 that highlights toDate = Replace(Cells(i, 3), ".", "/") when I run the debug.

The second is that no matter what I try to do I continue to receive error messages. Can I ask that one of the very talented excel knowledgeable people here look to find where I am making my errors?
Thank you much in advance with any assistance you can provide which will stop me from pulling out my hair.
 

More replies
Relevance 71.75%

Hi, I have zero experience with VBA but need to get the attached spreadsheet set up so that an email notification is automatically sent whenever an expiry date is within 30days.
In Sheet 2 (Required Compliance) I have entered the my name as the person to address the emails to, and my email address.

If possible I need to set this rule up for every sheet within the workbook, and for it to also update the Mail Status column to display whether or not notification has been sent.
Can anyone help me to set this up? Thanks in advance
 

More replies
Relevance 71.75%

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

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

More replies
Relevance 70.93%

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

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

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

I have different type of samples in design centers on an Excel spreadsheet that will expire on a certain date. I would like to set up an email alert to myself when they expire. Can this be done? HelpThanksJill

Answer:How to create and email alert for when something expires.

Look here and see if it helps:http://www.rondebruin.nl/win/sectio...For future reference, Excel questions are best asked in the Office Software Forum:http://www.computing.net/forum/offi...MIKEhttp://www.skeptic.com/

6 more replies
Relevance 67.65%

i am using a excel sheet. in that, days left are maintained against each roll no. if the days left reaches (suppose say as 3 ) i need a mail to be sent to 2 mail id's i wish using the roll no for which the day reaches the value 3. is that possible? i am using excel 2007 and microsoft outlook. kindly let me know.
 

More replies
Relevance 67.65%

So I need help on something very easy.
I need to have excel email just one person when a due date is met.
This is really simple
the cell in A1 will be the current date, aka =today(), and the cell in C1 will be the impending due date, like =A1+45, and B2 would be the function, in which =IF(C1=A1, "alert", "not due")

I need the email to be sent if B2 reads alert.

OS Windows 7
Office 2010
 

More replies
Relevance 66.83%

I have a project planner (excel 2003) and would like the spreadsheet to email (outlook 2003) me when I have an upcoming due date (preferably the day before).

E.g. Due Date 13.03.2013 would induce a reminder today (12.03.2013)

I won't be viewing the sheet daily and would therefore like this to happen even if the sheet isn't open. Is this possible?

The Due Date is listed in a column.

Are these actions possible? If so, how?

Thanks for your help.
 

More replies
Relevance 66.83%

Hye guys...i have excel file to control expiry date for technical documents. Every month, i need to send reminder email to the user on the expiry date, so they can review back the applicability of the documents to the user.

My question is..if there any way i can sent the email to the user automatically from excel file, one day before the expiry date.
Currently i m using Zimbra email for internal communication.

Appreciate your idea.

Thanks
 

Answer:Email alert from excel file

Hi, welcome to the forum.
I personally do not have any experience with Zimbra but have you checked the othet posts with similar questions?
There are quite a few and also some I helped with.
If you do a search in this forum you will see the answers and the code used which will maybe help you.
We can always carry on from there.
 

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

Hi everyone,

I'm new here, and kind need your assistant on this spreadsheet. Been searching all over from this forum but non are helpful.

As attached, I created an excel workbook and would like to have excel AUTOMATICALLY send to me and other colleagues as well a reminder to Outlook email which the password going to expire soon WITHOUT opening the workbook. Is it possible?

Can someone help me on this ? as I don't have much exp on VB. Thanks!
 

Answer:How Excel AUTOMATICALLY send alert to email when is duedate

May be this thread is what you looking for http://forums.techguy.org/business-applications/574148-e-mail-cell-data-excel.html
 

1 more replies
Relevance 64.78%

Good day,

This is my first time posting here.
I'm looking in automatizing my excel VBA to send out emails via Outlook on dates that i set in my excel workbook.

this is what i need this workbook to do.
Automatically run macro 7 days prior to date in column C.
not send email on due date (column c), but 7 days prior to due date.
change Font toArial, size: 10 , color RBG: (89,89,89))
add personal outlook signature at the end of email body.
Any help will be very much appreciated.
 

More replies
Relevance 64.78%

Hello I have extracted a large amount of data from Visio and have to compile email addresses from the data. One column has first names another column has the surname.The company email addresses are in this [email protected] All the formulas that I use advise that there is an error regarding the "_"Is anyone able to help with this? Thanks

Answer:create email addresses with a _ from excel

re: ...there is an error regarding the "_"...What error?With this data...
A B C
1 First Last Company
I used: =A1 & "_" & B1 & "@" & C1 & ".com"which returned:[email protected] turned it into a click-able email address hyperlink which opens my email app.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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

I have spreadsheet long list column of data with dates back to year 2005. I want to insert name of days beside the dates automatically. Doing it manually will takes a lot of time. Please help.

Answer:How to create days in a list of dates in exce

If your data is in A1, in B1 enter the formula: =DAY(A1)Then Custom format cell B1,Select Format,Select Cells,Select Number Tab,Select Custom In the Type box enter: ddd or dddd.Three gives you the short day SatFour gives you the long day SaturdayYou could also use the =TEXT() function to do the same thing.With your data in cell A1in cell B1 enter the formula: =TEXT(A1,"dddd")as with the Custom Formatting, Three d's gives you the short day SatFour d's gives you the long day SaturdayIn both cases, drag the cell down as many rows as needed.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 56.99%

Hi, I am having a lot of trouble creating this and would love some help. Basically what I need is to type in a worksheet is projects with a Title, Start Date and Finish Date. I then need this to be presented in another worksheet with number 1-31 in B1, C1 etc and Jan - Dec in A2, A3 etc. In the grid I would like areas shaded, for example 2nd feb to 28th march, with the Title to show in the shading: Based on what was entered in the previous worksheet. And also have a restriction that projects cannot overlap, if a project is extended say from 28th march to 2nd april it will push the start and finish dates of future projects forward.Also, have it so users can only enter project Title, Start Date and Finish Date; Have the choice of two colours for the shading on the calendar; And cannot edit anything else.Is this possible to do in excel and how would I go about it? Any help would be great! Thanks!

Answer:Create an advanced planner using entered dates

Try here for some templates that you might find useful:http://chandoo.org/wp/project-manag...http://www.vertex42.com/ExcelTempla...also look at the MS Office web site.MIKEhttp://www.skeptic.com/

2 more replies
Relevance 56.99%

I have a worksheet that I email on a daily basis as the message body (don't want to send the entire file). Unfortunately my very computer illiterate coworkers can't do this without messing it up.

I'd like a button when clicked does the following:

1. Opens Outlook
2. Sends the active sheet as the message body.
3: To: AMG-NM-CREW
4: Subject: Crew Check In
5. Closes Outlook

I'm a novice at visual basic, so please be specific how I make any macros.
 

Answer:Excel 2003 - Create a "Email" button

10 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%
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'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: 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%
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: 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%
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%

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%

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

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

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

Thanks for the help
puterputter
 

Answer:Dates in Excel 2007

6 more replies
Relevance 54.12%

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

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

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

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

??
 

Answer:subtracting dates in excel... help please!

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

3 more replies
Relevance 54.12%

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

Answer:EXCEL If else statement with dates

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

2 more 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
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 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%

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%

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

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

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

Answer:Excel-Counting with dates

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

Hello,
What a great help this forum is.
I know nothing at all about VBA, yet managed to get a macro in excel that allows me to send the "due date" emails that works almost perfectly for my needs. I based my macro on the code found here https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/
But i'm still missing one function in this macro for it to be perfect for my needs. I want it to send only one email to the "area manager" specifying all the items (serial number and location) that follow my due-date rule (instead of one email per item). I want it to send a list of items that are due this month for example.
Is it possible?
 

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%

Hi there

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

Range("K1").Select
Do
If Range("k1") <= Range("D9") then
ActiveCell.Offset(0, -1).Range("A1") .select
End If
Loop Until ActiveCell >=Range("K1") or ActiveCell = ActiveCell.Text

Cells K1 and Cells D9, C9 and B9 all have dates. Assume that the dates are as follows:
K1 = Sept 05, 2005
D9 = Oct 15, 2005
C9 = Sept 05, 2005
B9 = Aug 2, 2005
A9 = text

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

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

Please help.

Mario
 

Answer:working with dates in Excel

7 more replies
Relevance 54.12%

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

Answer:How to If Statements Using Dates in Excel?

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

3 more 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%

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

Answer:Computing Dates in Excel

Use the datedif function

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

Where
Date1 is Start Date
Date2 is the end date
Interval is one of the following: You must use the inverted commas

"m" Months
The number of complete months between Date1 and Date2.

"d" Days
The number of days between Date1 and Date2.

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

"ym" Months Excluding Year
The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.

"yd" Days Excluding Years
The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.

"md" Days Excluding Months And Years
The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year.
 

1 more replies
Relevance 54.12%

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

Answer:Excel not recognising dates

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

6 more replies
Relevance 54.12%

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

I want to make a new column of months from a date
I have a detached with column A as a normal date format 01/12/2006 DD/MMM/YYYY
I then in column B use the month function - so = month(A1)
it displays 12 - and if I go onto the Fx to see the expression helper / wizard
I get told that i get a number returned 1-12 here 1 = Jan and 12 =dec

so that i format the =month(a1) into a MMM-YY format and
get Jan-1900 - I'm cool with the 1900 as i have not specified YYYY but why do i get Jan returned instead of Dec

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

so I can choose and show month data

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

any help appreciated
 

Answer:Excel: Dates: Month

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

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

Answer:Excel sum formula with dates

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

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

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

start date end date
jane doe 1/22/2008 4/3/2008

February ?

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

Answer:countif excel between two dates

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

Hello to all,

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

My operating system is Windows XP.

Thanks,
 

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%

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

Answer:Excel coding using if for dates

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

7 more replies
Relevance 54.12%

Quick question.

Lets say I have a date.

11/01

Is there a formula that would return and IF statement?

For example

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

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

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

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

Answer:Solved: Excel Dates

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

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

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

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 want to sum numbers between date ranges how do i do this?

Answer:excel formula for dates

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

6 more replies
Relevance 54.12%

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

Answer:How do a lay out an EXCEL IF statement using DATES?

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

2 more 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%

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

Answer:Counting dates in MS Excel

What kind of entries are you talking about?

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

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%

Hi there.

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

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

Is there a simple way to do this?

Thanks in advance for any help.
 

Answer:Excel, dates and times

I think you'll have to Google for this

I can suggest the following link for starters:
http://www.cpearson.com/Excel/datetime.htm#WorkHours
 

2 more replies
Relevance 54.12%

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

Answer:Excel formula to add dates

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

4 more replies
Relevance 54.12%

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

Answer:Solved: Excel dates

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

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%

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

Answer:why does excel change my dates

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

3 more replies
Relevance 54.12%

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

Answer:if then statements with dates in excel

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

3 more 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%
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%

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%
Question: email dates

ive been noticing the past several days that i am receiving email but the date of the email is a few days earlier than todays date... i check my clock and cal.. they are correct... has anybody had this problem.... driving me crazy..(short trip... i could prob. walk it)
thanks
sd
 

Answer:email dates

Hi sabledog,

If you look at the internet headers of the email, you will notice all the different servers that your mail has been routed through. One of those servers probably held onto your mail, due to backlog or whatever, and that is the reason you see a date or time difference.
To view Internet Headers in Outlook, Open mail, click View, Options. At the bottom of the window you will see the Internet Headers box.

here's an MS Technet response concerning OE
OLEXP: Message Sent or Received Time Is Incorrect in Outlook Express

I do not know if you can view Internet Headers in OE.
 

2 more replies
Relevance 53.3%

Hi all,
I've spent a good few days reading posts on this site it seems between everyone here there a whole host of knowledge!

I'm here as I am having trouble with an excel sheet and I'm unsure how to solve it.
I am using excel 2007 on my laptop.

I've searched both this site and google for a few days now but I haven't found anything that seems to be what I need.
There is a thread on here that was helped by Keebellah and that is the closest I can find to what I am trying to do however I can't seem to edit the code in such a way that gets it working in my sheet.

Let me explain what I am trying to do.

My sheet is a training tracker which has a column of names of employees and some dates.
Specifically the dates are: Ideal WK4 date, Ideal WK8 date, Ideal WK12 date. These all have dates inserted.
There is also a column next to each of these that either says completed, or is empty.

I am trying to get Excel to email me when one of these dates is 7 days away or less, unless the column says completed. Then I don't want an email.
I want the email to basically say:
EMPLOYEE NAME is due for their WK? meeting within 7 days. Please schedule this in.

The employee name and WK4/8/12 should be pulled from the sheet.

Logically speaking, this is what I am looking for...
IF M(ideal date column wk4) = less than 7 days, send email with row data. If O=Complete don't send

IF Q(ideal date column wk8) = less than 7 days, send email with row data. If S=Compl... Read more

Answer:Solved: Emails from Excel using various dates

16 more replies
Relevance 53.3%

Excel in XP only appears to accept dates of 1900 onwards as proper dates, 1900 being 1 according to the help files. Doing family history I want the prog to accept dates prior to 1900 and to be able to sort them. Is this possible, please

Answer:How can I sort Excel pre 1900 dates

Excel doesn't make it easy. Apparently this is because Excel was originally designed to be compatible with Lotus 123. There is a sort of work-around here click here

8 more replies
Relevance 53.3%

HiI want to ccreate a calender which shows all the months, days and dates.And when one click on a day of the month another box shows which explanes the task of the day.One should be able to combine different data with different dates.The box is on the same sheet as the calender.i.e. : Clicking on Thurday jan. 21. 2010, on the calender, the boxs shows information on tasks etc. done that specific day

Answer:combining calender dates with a Box In Excel

re: Clicking on Thurday jan. 21. 2010, on the calender, the boxs shows information on tasks etc. done that specific dayThis makes sense, although it would be helpful to know the extent of the "tasks" for that day. Are we talking about pages of tasks or a simple task like "Back Up Data"?re: One should be able to combine different data with different dates.What does this mean?

7 more replies
Relevance 53.3%

Hi,
I have disabled every auto-correct and auto-format option I can find, but Excel still won't stop changing my numbers to dates. If I type 1-2, it automatically changes it to 2-Jan, and so on. I'd really appreciate any suggestions you can offer me. My bio grade appreciates it, too. Thanks.
 

Answer:Excel Keeps Changing Numbers to Dates

Have you tried formatting the field(s) to text?
 

2 more replies
Relevance 53.3%

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

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

Any ideas???
Operating system: Windows Vista
 

Answer:Excel 2007 displayed dates

9 more replies