Computer Support Forum

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

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

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!

Relevance 100%
Preferred Solution: Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

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: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 90.77%

In excel I am trying to compare a column of various dates against the current date (reported in one cell using the =today() function) and if the dates in the column is past today's date it will report "Late" in the adjacent row or if the date in the column is the same as the current date it will report "Due" in the adjacent row. "If/then" doesnt seem to work. I have thousands of columns of dates but simplied exampleCurrent Date = 04/21/2014Column A, Column B, Due Date, Status, 04/12/2014, Late04/21/2014, Due

Answer:Comparing a date in column against today's date

First, a posting tip...If you click on the blue line at the bottom of this post, you can read the instructions on how to post data in this forum so that the columns line up and your data is easier for us to read. It may take some trial and error via the Preview screen, as explained in the instructions, but it can be done. Second, you told us what you want if the date to be checked has past or is the same as TODAY(), but you didn't tell us what you want if the date is later than TODAY().I assumed you want a blank cell.

A B
1 =TODAY()
2 Due Date Status
3 4/12/2014 =IF(A3<$A$1,"Late",IF(A3=$A$1,"Due",""))
4 4/21/2014 =IF(A4<$A$1,"Late",IF(A4=$A$1,"Due","")) Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

3 more replies
Relevance 80.91%

=IF(ISNUMBER(MATCH(D13,Status!B:B,0)),INDEX(Status!BD:BD,MATCH(D13,Status!B:B,0)),0)BD column is a date. I have two colums with the Column BD reference.1.) If the date is in the future it will populate a blank and if date has past it will produce date in cell.2.) If the date is in the future it will populate date and if in the past it will produce blank.I have tried adding >today(),"" and I guess I've stared at this too long and can not fix it. Or I'm adding it in the wrong place, missing a comma or something. Appreciate your help. :-)

Answer:BD column is a date. I need to add a statement ....today

I'm having a bit of trouble figuring out what you are trying to do by just seeing the formula.Would it be possible to post a few pieces of example data and the expected output based on that data?Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 79.75%

I have the following macro below which works great for when any value is entered in Column C, it time/date stamps the adjacent cell in Column D. However, I need it to do the same thing a few columns over (ie, when any value is entered in Column H, it will time/date stamp the adjacent cell in Column J)

For the life of me, and cannot get it to repeat . Any ideas? I'm a bit new at this, but thanks in advance for the help. Feel free to spell it out - I won't be offended.

Thanks! Here's the current macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C3:C9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

 

Answer:Solved: Excel Macro - Repeat Time/Date Stamp for Different Column?

6 more replies
Relevance 79.46%

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

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 Today's date (year) - cell date (year

11 more replies
Relevance 75.4%

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

Hi Everyone,

I have a date column in my spreadsheet and there are several actions that I would like to apply to it -

1). Auto populate the date column with the current date only when the column to the left has data entered into it (please see below)

2). Make the date column read only so that it cannot be modified by other parties.

Any help will be greatly appreciated.

Kind Regards,

Davo
 

Answer:Solved: Automatically enter date into a column and make that column read only

6 more replies
Relevance 104.96%

HI, I hope someone can help me here. I have a table. Column A is a month (numeric), Col B is year (numeric), Col C is a monetary value. For each month there are a variable number of entries, so the table would look something like below (shortened example - each month normally has about 70 to 80 entries:A B C1 2010 35.991 2010 27.991 2010 15.992 2010 12.992 2010 45.993 2010 33.993 2010 23.993 2010 56.99Each month, new data is added so the columns keep extending.OK, to the right of this table, I sum each month, i use K,L,M columns for thisK contains the calculation, L the month, M the YearK L Msumifs 1 2010sumifs 2 2010sumifs 3 2010I am using SUMIFS at the moment, as follows:K1 = SUMIFS($C$2:$C$2000,$A$2:$A$2000,L2,$B$2:$B$2000,M2)K2 = SUMIFS($C$2:$C$2000,$A$2:$A$2000,L3,$B$2:$B$2000,M3)This does work, but I continually have to adjust the "2000" in the example to be the full length of the columns as they continues to grow as i add new data for new motnhsI am sure there is a "proper" way to do this, but I just don;t know how. Any help would be very much appreciated.

Answer:Excel sum by date on growing column

Use just the column as a reference. Example A:AI'm still learning everyday.

3 more replies
Relevance 104.96%

I have a spreadsheet with bank transactions and want to insert a blank row every time the date in column A changes. Can someone help me write a macro to do this? Thanks!

Answer:excel: insert row when date in column changes

Hi MarvinHere is something that might work although you should try it out on a copy of your workbook.
Sub AddRow()

NoRows = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To NoRows

If Cells(i, 1) <> Cells(i - 1, 1) And Cells(i, 1) <> "" And Cells(i - 1, 1) <> "" Then
Cells(i, 1).EntireRow.Insert
i = i + 1
End If
Next

End Sub

3 more replies
Relevance 104.96%

The column heading in Win 10 File Explorer is 'Date'. But which one?

I've always been used in XP to the default being 'Date modified', but it seems that here it's Date created? Several re-saves have left 'Date' unchanged.

--
Terry, East Grinstead, UK

Answer:File Explorer Date column - whiich date is it?

Hi:
What is the column in Windows 7 explorer? It matches no date column from Windows XP explorer - Super User

2 more replies
Relevance 104.14%

I have a column with dates in format yyyy/mm/ddI copy dates from other files that has the date format yyyy-mmI would like the dates with the format yyyy-mm to change to yyyy/mm/dd when I paste them in my column. I have already set the format in that colum to be yyyy/mm/dd, the way I want it but still the dates I copy does not changes their format.Please if anyone have some ab code that could help me with this do tell me and were I chould save it. I have already tried many ways but does not get it to work, and I can't download any converter to do the job for me. So Thank you for helping.

Answer:Change date automatic in an excel column

I copy dates from other files that has the date format yyyy-mmIs the other file an Excel file?After you have copied it, does it left justify in the cell?If you apply a Custom Format of General does it change into a number?MIKEhttp://www.skeptic.com/

18 more replies
Relevance 103.73%

After running a large update of windows on May 18 I found that ALL my files in ALL my folders are showing a Date Modified of May 18 2013. Can't find a similar report of this problem. Any ideas?
Using Windows 7 Professional 64-bit

Answer:File Date Modified reset to today's date after windows Udate

Would this help? enter link description here

1 more replies
Relevance 103.32%

I need to return a number associated with a date based on today's date. For example: A1:A24 are numbers 1 to 24 and B1:B24 contain the 15th and 30th for each month of the year; 15-Jan, 30-Jan, 15-Feb, 28-Feb, ... 30-Dec.Cell C1 needs to return the number from A1:A24 based upon today's date compared to the date in B1:B24.For example today is 08/21/15, what number should be returned in Cell C1. It should be 15 as 08/21 is less than 30-Aug.message edited by jmichels

Answer:Return a number based on today's date compared another date.

Try this formula, see how it work for you:=INDEX(A1:B24,MATCH(TODAY(),B1:B24,1),1)This will work for most dates, but errors out if the Date is 01/14/15 or less.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

4 more replies
Relevance 102.91%

How do I convert a date that appears in 3 columns to a single column date i.e. column A has MONTH( Jan shows as a 1, Feb as a 2), column B has DAY(I.E. 3 OR 4 OR 10) and column C has Year (2 DIGITS 2010 shows as 10 or 2011 as 11)..want to convert to signle column 01/03/10...

Answer:in excel convert 3 column date to single colu

Concatenation (&) of values from multiple cells creates a "text string".DATEVALUE converts a text string into a date as long as Excel can recognize the structure of the text string as a date.Putting those 2 facts together, try this:=DATEVALUE(A1 & "/" & B1 & "/" & C1)

4 more replies
Relevance 102.09%

There is a file shared by my group where we track work orders scheduled by date. Column 1 contains a list of dates, in order, like so
5/30/06
5/31/06
6/01/06
etc.
Sometimes, when you open the file, someone else has scrolled up or down the page, so if you want to view today's date, you have to scroll up or down to find it. What I would like to do is create a macro that takes you to the cell containing today's date. I tried creating a macro that did the following:
1- Go to a cell containing a formula =TODAY()
2- Copy the value of that cell (this will be today's date)
3- Open the Edit --> Find window
4- Paste in the value I just copied
5- Click find

This worked great on the day that I created it, but today, it still goes to yesterday's date. When I look at the macro, what it's doing is always searching for the value that "TODAY" had on the date I created the macro. It's not smart enough to actually paste in the value I copied, but instead sees it as FIND: WHAT="5/30/2006"

Is there a way to use the Find or CTRL-G, or any other formula to take me to the cell containing today's date? It seems like this should be a simple thing, but I just can't figure out a way to make it work.

Thanks for any help you can offer!
 

Answer:Excel: Go to cell containing today's date

If you have a cell on the worksheet with =Today() in it, that cell will always show the current date. You look up that in your macro and use that to find your current date in the data.
 

3 more replies
Relevance 102.09%

I am looking to make an IF statement for the following:In cell J1 there is a date in it and I want I1 to read "Calibration Due" if the date in column J is less than or equal to 45 days from today's date, and remain blank if this is not true.Any help would be great!Thanks!

Answer:IF statement in Excel using today's date

Try:=IF((J1+45)>=TODAY(),"Calibration Due","")MIKEhttp://www.skeptic.com/

4 more replies
Relevance 101.27%

Hey guys,I'm working on an Excel Spreadsheet that maps out due dates.You enter the date a document was submitted and it fills out the course that a user has to follow for the remaining timeline of that document.With the due dates, I want to color-code warning dates, and past-due dates.Example:01/01/2012 - Document submittedExcel will show the submission and the due date01/10/2012 - Follow-Up dueSo let's say the C5 is the user input box for the date.C11 is the box that declares the due date for follow-up.C12 is a reference box as to the "status" of it's follow up.I want to box C12 change colors accordingly;Light Yellow - 5 to 1 days before dueYellow (darker) - on due dateRed - any date after dueI used the following conditional formats:Red =C11<TODAY()Yellow (darker) =C11=TODAY()These both worked out fine for me and do what I wanted.I am lost on the light yellow variation though.None of the formulas I tried for the TODAY() function and the date range have worked.Any help if appreciated, Thanks.

Answer:Today Function and Date Range in Excel

How about this:=AND(C11>TODAY(),C11<TODAY()+6)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 100.04%

I have an excel spreadsheet with lots of dates on it. One time, I saw someone create an arrow or bar that automatically went to today's date on the spreadsheet, so a person would not have to scroll down through past dates to get to today's date. I tried to " create a new rule" in conditional formatting so that today's date was highlighted yellow. The highlighting feature works, but when I open the spreadsheet I am not taken automatically to today's date. Is that some other feature? Can it be done on the excel in google drive?
 

Answer:Go to today's date automatically in Excel in Google Drive

Hi,

I'm not sure about the "Conditional formating". But, You can have a code in the "Thisworkbook module" that will go to today's date.
 

1 more replies
Relevance 99.63%

I have recently had to reformat by Window XP Laptop reqiring a reinstallation of all ProgrammsOutlook 2003 has since this reload returned a none date when sending emails to the outbox.It should change the email into italics and have a send date by each email.Is there a simple setting to change this back.I have already tried uninstalling and reinstalling Office 2003 but to no avail.Does anyone have any ideas please?Thanks

More replies
Relevance 98.81%

Help

I'm mail merging with Word a huge contract document and need to put in the phrase 'this contract is made on the [seventeenth] day of [April] [2010]'. Even in a custom format I'm struggling. When the contracts are printed off they need to be 'today's date' ie the day they are printed.
Thanks
 

More replies
Relevance 98.81%

I have listed sequentially a column of dates (3/1, 3/2, 3/3 etc.) Adjacent to this column I have listed specific sales goals associated for each day. I need help in getting excel to find today's specific day and display the associated sale goal for that day in a designated cel on top of the page. Any help would be greatly appeciated!

Answer:Need excel to find today's date and display associate data

Try using VLOOKUP and set your lookup_value as TODAY().e.g.=VLOOKUP(TODAY(),$A$1:$B$20,2,0)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 97.58%

I have a field for data a appoinment is for in an access table and i need to apply validation to allow entry of todays dat or anydate after today.
I have tryed stuff like ">=Now()" or ">Now() and =Now()" but nothing works please help
also can anybody help with my other problem about No changes to certain field in a table or form
see

http://forums.techguy.org/business-applications/495256-access-no-editing-certain-field.html
THANKS
 

Answer:Solved: Access Date Today or after today

If this is the same field as the first post then you will be stuck with the default value of Now() or Date().
If it is a different field then in the validation property use >=Date().
Date() is used when you do not need to store the "Time" as well as the date, Now() stores the Date and the Time.
 

2 more replies
Relevance 97.17%

Hi,

I have a column containing set of dates; i need to get the dates exactly after two months to appear in next column automatically. e.g, I have cell with date 6-Feb-09, I need 6-Apr-09 to come in next cell automatically. Which formula can I use it?
 

Answer:Solved: 2 month later date in next column

6 more replies
Relevance 97.17%

Currently have Excel 2003 and have day of week in one column (F3:F30)- Formula (=j3-d3) formatted as Number/Custom/dddd.
Also have date in Column G which represents the date I arrive in a town, and column J is the day I depart.
In columns F and I are the days relating to dates in columns G & J.

If possible I would like "Saturday 18 July 09" to appear in cell F3 and "Sunday 19 July 09" in cell I3 with resultant formula being dragged down page as necessary.
Columns G and J to be cleared for other useage.

Copy of spreadsheet attached

Pedro
 

Answer:Solved: Day of week and Date in same column

Solved.
Went to Format /Number/Custom and inserted the option

HTML:
[$-C09]dddd, d/mm/yy

This produced

HTML:
Sunday, 19/07/09
Pedro
 

1 more replies
Relevance 96.76%

I have an equipment list and I would like to be able to be prompted 1 week prior to the date that my calibrations are due without having to remember to check all the time.Can you please help me set it up so that an email alert can be sent saying that a certain piece of equipment is due for calibration within 1 week.

Answer:how to get excel to send me an email when a due date arrives

I have only minimal skills with Macros but see if this site gives you some ideas:http://www.rondebruin.nl/win/sectio...MIKEhttp://www.skeptic.com/

6 more replies
Relevance 96.76%

Hi there,

I have a workbook which i would ideally like to send an automated mail when the date is within 30 days of "Todays date" .
I have found something similaar on past posts whichprints certain cells to an email but is triggered by a button press not date, but wondered if anyone could adjust it for me as my excel knowledge is very limited.
I really am struggling.

The password for the spreadsheet is Kalibratedbyme (capital K)

Best regards and many thanks!
 

Answer:macro to allow a date to send an email in excel

The content is different but why are you duplicating a post?
 

3 more replies
Relevance 95.53%

I've read the previous post with the same issue, but I'm unable to understand how to use the other codes posted within my product. I would like to send an email based on a date. I will attach my document so it is easier for me to explain the requirement. Columns L37-L45 have due dates - I would like the email to be sent 60 days prior. I have posted some mock emails in R37-R45 and the email message in the EMAIL workbook tab. Any assistance would be greatly appreciated.

Thank you so much!
 

Answer:Auto send an email based on date in Excel

Welcome to the board.
I've had to save it as 2003 version but the code works under 2007

See attached my copy of your sheet with the code in ThisWorksheet module.

This just a simple way of doing it and you will have to edit it for your needs but maybe it can put you on the right track.
 

2 more replies
Relevance 95.53%

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

Somehow the date on one of my old Excel files has turned from 1999-10-04 (i.e. October 4, 1999) to 4617-04-11. How can I change all the rest of the incorrect dates back to what they should be? This is the only date that I can confirm. And I have no idea what the rest of all the dates should be on this spreadsheet. TIA.

Answer:Excel date problem - to convert incorrect date to original

I have no idea what the rest of all the dates should be on this spreadsheetThen how do you purpose to change them, when you don't know what year they are supposed to be?Somehow the date on one of my old Excel filesAny Macros running that may have in some way impacted the dates?How is the data stored? As Text or as a Date?MIKEhttp://www.skeptic.com/

5 more replies
Relevance 95.53%

I'm working on a spreadsheet that lists monthly recurring expenses. I have one cell on the sheet listing the current date (using the "=Now()" formula), but I'd like to have the other cells change at certain times throughout the month.

For example, if one cell currently says "April-15", when the actual date reads "April-16", the respective cell would change to "June-15". This way, whenever the spreadsheet is viewed the next recurring payment date will be seen.

I know this is the wrong tool for something like this - I'm using SharePoint at the office with a calendar list with workflows that shoot off emails in a much better put together format - but this is just for home use and I'd like to keep it all in one document if possible.

Any ideas or suggestions would be greatly appreciated. Thanks in advance.
 

Answer:Excel 2010: Cell Date Change with Current Date

The way I read it was that you have fixed recurring payment dates, so you just want to know the next date based on the day today. Hope the attached might assist.
You need to list the payment dates somewhere and then the formula looks up the next date. You might want to look up today plus 1 (or something) to reflect the fact that if the next payment date is today, there's a good chance you'll not get any unplanned payments on that run.
 

1 more replies
Relevance 94.71%

Can anyone help me please?I'm trying to get excel to send an automated email from outlook when a due date comes about in certain cells Desperately seeking help on this Thanks

Answer:Automated emails when cells in excel are at specific date?

Try here:http://www.rondebruin.nl/win/sectio...should give you a starting point.MIKEhttp://www.skeptic.com/

4 more replies
Relevance 94.3%

I have a file that lists a Julian date of, for instance, 37573. How do I convert that into something like 11/06/02?

Thanks in advance!
 

Answer:Julian Date--How to convert to calendar date in Excel

That's not a Julian date, Beth.

That is actually a date value that Excel provides.

That's how many days it's been since 1/1/1900.

To change it to a real date, you should be able to select that cell or entire column, hit Format-Cell, Number tab, and choose Date, and then any one of the date formats.

Julian dates are what numbered day of the year. For instance, January 3, 2002 would be 3.

Learn lots about dates and times in Excel:

http://www.cpearson.com/excel/datetime.htm

Be prepared to be there for a while.

Happy Thanksgiving!
 

2 more replies
Relevance 94.3%

So I have this excel spreadsheet and an example of it basically looks like this:
Project Phase Person Capacity Start Date End Date
A Design 1 25% 1/15 3/16
A Model 1 25% 3/17 5/11
A Design 2 25% 1/15 3/16
A Model 2 25% 3/17 5/11

I'm looking to track a monthly outlook of capacity % over time for each person (in a pivot table), but I'm unable to get it exactly the way it should be. Essentially I would like it to look like this below:

Dates
Person Jan Feb Mar Apr May
1 25% 25% 25% 25% 25%
2 25% 25% 25%

Is this even possible, either in a pivot table, or even using formulas?
 

Answer:Excel 2007 - Start Date and End Date question

You should have posted that in "Business Applications" here: http://forums.techguy.org/16-business-applications/
 

3 more replies
Relevance 94.3%

So I have this excel spreadsheet and an example of it basically looks like this:

Project Phase Person Capacity Start Date End Date
A Design 1 25% 1/15 3/16
A Model 1 25% 3/17 5/11
A Design 2 25% 1/15 3/16
A Model 2 25% 3/17 5/11

I'm looking to track a monthly outlook of capacity % over time for each person (in a pivot table), but I'm unable to get it exactly the way it should be. Essentially I would like it to look like this below:

Dates
Person Jan Feb Mar Apr May
1 25% 25% 25% 25% 25%
2 25% 25% 25%

(Sorry, the text keeps left aligning so person 2 should actually have 25% under Mar, Apr, and May.)

Is this even possible, either in a pivot table, or even using formulas?
 

Answer:Excel 2007 - Start Date and End date question

16 more replies
Relevance 94.3%

I am looking to set up a macro or formula that basically inserts the current time when a cell is filled SO:

when a2:a100 is populated with any thing then the corresponding C cell will be populated with the time.

AND

when e2:e1000 is populated wiith anything then the corresponding D Cell will be populated.

Currently I am using this macro.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A6:A100")) Is Nothing Then
With Target(1, 4)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub

which works great for the A and C cells

but it does not work for the other one.. how do i add and modify this so all my needs are met.
I need another 2 sets of 2 macros accross the sheet for time entry.
 

Answer:Excel date auto populate date/time

11 more replies
Relevance 94.3%

Hello, I have spreadsheet with a number of dates and times ie (11/19/07 8:57 AM) in column A and data going along with each of these dates and times from columns B-CK. I am trying to limit the list to show the information that is for all times after 2:45PM. The problem is I cannot sort by time and the dates attached to all the times span from 2004-2007. Please help!
 

Answer:Stripping the date portion of date/time in excel

7 more replies
Relevance 94.3%

I received a .csv file from a company with a date field that looks like this: Tue Jan 03 12:12:20 PST 2012I have tried using the Data Type options to change this to a mm/dd/yyyy format but it will not apply. I tried converting it to a serial number using*mm/dd/yyyy and it will not work ~ nothing happens. I tried using a DATEVALUE formula but keep getting a #VALUE error .. I obviously don't know how to lay it out. Can anyone give me the DATEVALUE formula for this example? I am not sure what "right", "left" or "mid" represent or how to count the value placements. THANKS!!!

Answer:How do I convert a complex date to a simple date in Excel?!?

Try this:With your date in cell A1,First, format cell B1 as a DATE fieldRight click your mouse, select Format Cell, Select Number Tab,select Date, choose the date format you want.Next, in cell B1 enter the formula:=DATEVALUE(MID(A1,5,3)&" "&MID(A1,9,2)&", "&RIGHT(A1,4))That should give you what your looking for.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 94.3%

Excel 2013 and Windows 10 Pro

Today is May-14

Date format is set to show: Mar-16
Pressing Ctrl+; shows: 05 14 2016 in the cell
and it converts to: May-16 after I exit that cell.

Any ideas as to how to fix this or why it adds 2 days to the date?

I don't do much on computer now because of health but when I do and there is a problem, I know some of the experts are here.

Thank you in advance, Ron
 

Answer:Excel Date shortcut posts proper date and then changes

Hey Ron,

This should be a fairly simple one. I'd hazard a guess and say the May-16 is the month and year rather than the month and day.
If you click the format drop down as shown below, and click the last option for more formatting options.

In the box that pops up, navigate to Custom using the left hand panel to navigate.
Here you can make your own (or use a preset if you wish) format to suit your needs.

Here is a quick key to help you get by:
dd = Day in Number format
ddd = Day in Short text
dddd = Day in Long text
mm = Month in Number format
mmm = Month in Short text
mmmm = Month in Long text
yy = year in 2 digits
yyyy = year in 4 digits

You can separate these with pretty much whatever you want.
All of this data is input into the "Type" field as shown in the picture above.

See the picture for an example, Ironically it is May 16th today so it will appear the same as your issue does, but rest assured, as long as you have those D's in there, it will display the day!

Best of luck,
Let us know how you get on,

Xsage
 

1 more replies
Relevance 94.3%

Hello,

I have a large report of application forms which have been submitted including the date when they were submitted however the dates are formatted incorrectly.

1. Although the cell formatting is dd/mm/yyyy the data is being shown in mm/dd/yyyy which means the dates are incorrect (e.g. 2nd January would read as 1st February.)
2. As a result of this dates which are passed the 12th of a month are not accepted as dates (it reads the day figure as months and there is no 13th month etc)

I have tried to use the text to columns function but that makes no difference and I have tried to extract just the dates but I've had no luck.

Can anyone help, I've attached a small sample of the data.

Thanks

Daniel
 

Answer:Exported date field not recognized as date in Excel

Where does the data come from?
What do you get if you have no cell format?
 

3 more replies
Relevance 94.3%

Hello. I have a cell on my spread sheet which calculates an earliest date due. The cell references other cells to achieve this and looks like this '=C51+(F8*7)' where cell C51 is a date field in the format DD/MM/CCYY and contains the date 26/12/2015. And cell F8 is a number field which contains the value '4'. This value '4' is indicating a number of weeks. So the calculation (F8*7) gets me the number of days and I then add those days to the date in cell C51 to get 23/01/2016.I want this cell to output the current date if the calculation above results in a date that is in the past. Can anyone tell me if there is a simple way to achieve this? Thank you.

Answer:Excel: Substitute a past date with the current date

If I understand you correctly, this should get you what you want:=IF("your calculation"< TODAY(),TODAY(),"your calculation")However, the TODAY() will update every day to always show the current date, assuming "your calculation" results in a date that is in the past.In other words, it will not lock in the first date it calculates. It will always display "today's" date.Is that what you want to see?One other issue: As explained at the link below, the TODAY() function is "volatile". That means that is will recalculate every time the sheet calculates, even if none of the arguments have changed. A large number of volatile functions can slow a worksheet down.https://msdn.microsoft.com/en-us/li...Volatile and Non-Volatile Functions

Excel supports the concept of a volatile function, that is,
one whose value cannot be assumed to be the same from one
moment to the next even if none of its arguments (if it
takes any) has changed. Excel reevaluates cells that contain
volatile functions, together with all dependents, every time
that it recalculates. For this reason, too much reliance on
volatile functions can make recalculation times slow. Use them
sparingly.

The following Excel functions are volatile:

NOW
TODAY
RAND
OFFSET
INDIRECT
INFO (depending on its arguments)
CELL (depending on its arguments)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 92.66%

Does anyone know how to convert a date entry from Unix format to Gregorian calendar format?

I have a long table with Unix values for date and want to plot a count of row entries within a certain date range...

Any ideas?
 

Answer:Excel: Unix date to gregorian date

Also... how do I count the number of occurrences of similar entries in a column... i.e.

1
1
1
2
2
3
3
3
3
4
4

I want to be able to count the number of occurrences of 1, then the number of occurrences of 2 etc etc
 

2 more replies
Relevance 90.2%

When items are loaned out we want them returned in 30 working days (approximately 6 weeks I'm not going to bother trying to calculate holidays) but to only come back on a week day Monday through Friday.

Field Name ReturnDate_Fld this needs to be calculated as 30 working days from the current date

or should I not worry about the extra effort and use the approximate by adding 42 to the BorrowDate_Fld (plus 42 days = 6 weeks)

Should I worry about this in a form or only set it as field in the report/query?
 

Answer:Solved: Access 2010 Set a date field to today plus 30 working days

16 more replies
Relevance 89.38%

Since installing Windows 10 - previously on Windows 7 - all dates in Excel have turned into the American date format i.e. previously 12/08/15 in a cell displayed as 12th August 2015. Now it displays correctly but the cell contents have changed, so, in previous example, the cell now contains 08/12/15. How can I put this right? I have gone into anything I could see re language and have ensured it is English (United Kingdom) but this hasn't made any difference.

Answer:Windows 10 has turned my British date format in Excel into the American date format. How can I get it to turn back?

Freeeeeeeeeeeeedom!

10 more replies
Relevance 89.38%

I am frustrated with the following problem.
I need to convert the date to US formatting.
I receive a file in .csv format and the dates come as text and a normal date format all within the same column.

Example:

12/4/2012 23:16
12/4/2012 23:21
13/04/12 12:04 AM
13/04/12 12:52 AM
13/04/12 3:20 AM

The top 2, Excel reads as date. The bottom 3 excel reads as text...I need the bottom 3 to read as date.

Please HelP!!!
 

Answer:Excel 2010 UK Date to US Date

11 more replies
Relevance 88.97%

First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):
Code:
Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)

On Error GoTo Handle_err

Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim NextQtrDate As Date

'The code adds one month to datenextpayment at start of each month. Or yearly or weekly

... Read more

Answer:Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

16 more replies
Relevance 88.97%

hello to everybody

I need batch code to check if date is modified in three files and that date should be compared with current date (%date%).If so,do some job,otherwise quit (exit /b)

I found a bunch of solutions but what troubles me is date format.
On my machine it is dd.mm.yyyy

Thanks in advance.
 

Answer:Solved: batch to compare file modified date with current date

10 more replies
Relevance 88.56%

Hi, I am hoping someone can help me with a macro for Excel 2007. I wish to "reverse" the following macro, so that it sends the contents of an entire column (in this case the second column) back to the previous column as Cell Comments, without interfering with the contents of the cells.

Sub CopyCommentText()
Dim CmtText As String
Dim CmtCount As Integer, i As Integer
Dim CommCell As String

CmtCount = ActiveSheet.Comments.Count
For i = 1 To CmtCount
CommCell = ActiveSheet.Comments(i).Parent.Address
Range(CommCell).Offset(0, 1) = ActiveSheet.Comments(i).Text
Next i
End Sub ​
 

More replies
Relevance 87.74%

I want to know an exel formual for the following - Any help would be great

Date Range
01/12/2013 - 03/14/2013

How many days from the date range 01/22/2013 - 02/21/2013 are in the above date range

So answer for the above would be 31

Thank you for your help!
 

Answer:Excel 2010 - Count # Of Days In A Date Range That Are Part of Another Date Range

Hi LucianC - try entering this info:
Start Date in A1
End Date in B1
=DateDif(A1,B1, "D") in C1
 

2 more replies
Relevance 87.74%

Hello, I am having trouble finding the previous 2 days totals in a query. I have a totals query with the following fields:
JobID - Criteria: Between 500 And 599
WorkType
JulianDate
Batch
TotalProcessed - calculated field, grouped on Sum. sums field entry
ProdDate - Short Date - Grouped on Where - Criteria Date()-1 Or Date()-2

Today's Julian Date is 199 so query should give me Julian Dates 197 & 198, but instead returns Julian Dates 188 - 196.

Thank you for your help!
 

Answer:Solved: AC2007 Find Date()-1 And Date()-2 in Query

The criteria that you should use for a date period is
Between Date()-1 and Date()-2
 

2 more replies
Relevance 87.74%

I have a Date/Time field that I need converted to just a Time field.

It comes back with 2/11/12 0:00:00

I need just the 2/11/12

I thought about changing it to a text field but I think there will be an issue with the month because it does not bring back a 0 in from the months with only 1 digit.

This table that I am creating is being sent to a txt file and that is where I see the issue. It looks good in Access, but not the txt file.

Any suggestions?
 

Answer:Solved: Access - Convert Date Time to Date

10 more replies
Relevance 86.51%
Question: date column

This morning I updated to the latest BitDefender and while doing so their instructions did not indicate to not select uninstall as the installer would complete this task automatically even though a message did pop up asking me to uninstall. I believe this was the reason for the errors that followed regarding this new install.
I also believe this may have caused the problem in windows 10 control panel uninstall date column becoming empty.
Prior to this BitDefender issue, the windows 10 control panel uninstall date column was not empty.
I have searched extensively for why this date column would be empty, but no answers seem available.
Obviously, this is not an issue with BitDefender that I need to contact their support; it must be something with this OS.

More replies
Relevance 86.1%

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

The workbook is a log of video clips from a granny cam. It spans a period of approx 3 weeks, there is a row for each clip - a total of approximately 1900 clips. There are columns for date, time, clip no., length, and notes on the clips. Our problem is the date column is arbitrarily changing random dates. They are easy to spot b/c of the time column, since the videos were motion activated that dates would obviously change at 12:01 am. The problem for ex.will be a date or several dates of 8/16/08 mixed in with 8/15/08, and it progresses that way throughout all the rows in the workbook with no discernable pattern. The workbook also updates the dates with daylight savings time changes. (I would like to stop that as well).

Answer:Random changes in date column

Are you the creator of this spreadsheet?If not I would speak to the person who created it and ask that they modify it to suit your needs.Sounds like you might have several macros running, if it auto updates.MIKEhttp://www.skeptic.com/

4 more replies
Relevance 84.87%

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

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

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

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

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

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

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

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

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

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

I am changing a current report we do from excel to access. I have a column for sales for the day, sales for the same day last week, week to date sales, month to date, and year to date sales. I have a form which i enter the sales date into, it runs the queries and puts them into the report.

Right now, I have to enter the parameter for the beginning of the week date too. I am trying to make it automatically enter the beginning of the weeks date (Monday) based off of the one date that I enter.

The way it figures the month to date is from queries for each month Example( >=#6/1/2010# And <=[FORMS]![REPORT DATES]![ZZZDATE])

Week to Date = (>=[FORMS]![REPORT DATES]![WKDATE] And <=[FORMS]![REPORT DATES]![zzzdate])

How do I make it find the beginning of the week, without having to enter the parameter?

I am using Access 2003 on Windows XP.
 

Answer:Week to Date Sales Column

Charliana, wlecome to the Forum.
I almost missed this question as it does not have Access in it's title.
You can calculate the Monday's date based on the Curent Date on the Form and use that in the Query's Criteria.
You can also use the
between #6/1/2010# And [FORMS]![REPORT DATES]![ZZZDATE])
in the query
 

1 more replies
Relevance 84.87%

I have created a macro that filters information and have created a submacro that updates information after the macro runs. Here is the submacro:

Sub macro1()
For MY_ROWS = Range("L65536").End(xlUp).Row To 1 Step -1
If Range("L" & MY_ROWS).Value = "CANCEL" Or IsDate(Range("L" & MY_ROWS)) Then
Rows(MY_ROWS).Hidden = True
End If
If Range("L" & MY_ROWS).Value = "N/A" Or IsDate(Range("L" & MY_ROWS)) Then
Rows(MY_ROWS).Hidden = True
End If
Next MY_ROWS
End Sub

What I need now is to add a column in front of A1 and calculate a new date, which would be the date in column L minus two days. Is this possible or would I need to have column A blank 1st rather than inserting a new column? By the way, column A already has data.

Thanks for your help.
 

More replies
Relevance 84.87%

lets say I have columnslike,name code date statusabc 132 27/05/2017 00:00:00 Activeneed to change the date to sysdateI need to create a batch script for thismessage edited by Gourab

Answer:How to change date of a column in a .csv file

Batch? Really? Not VBScript? Not PowerShell?Also, you say you have a .CSV, but I don't see any commas. How To Ask Questions The Smart Way

2 more replies
Relevance 84.05%

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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