Computer Support Forum

Auto create time and date in Excel 2010

Question: Auto create time and date in Excel 2010

In excel 2010 new sheet, I would like to enter order number on column A1 then column B2 will show time and date automatically then after entered data then A1 and B1 should locked the cell and won't change anymore. Is this possible to do it?Thanks

Relevance 100%
Preferred Solution: Auto create time and date in Excel 2010

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 (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Auto create time and date in Excel 2010

If B2 will be empty the first time an order number is entered, then this code will enter the date and time once and will not change for subsequent changes to A2.If there will already be something in B2, then things get more difficult and I will need more specifics.(BTW...I am assuming the A2 and B2 are just examples. The code actually applies to the entire Columns A & B)
Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if change was made to Column A
'and respective row in Column B is empty
If Target.Column = 1 And Cells(Target.Row, 2) = "" Then

'Unprotect Sheet, Enter Date/Time in Column B, Protect sheet
ActiveSheet.Unprotect Password:="zzzzz"
Cells(Target.Row, 2) = Now
ActiveSheet.Protect Password:="zzzzz"
End If
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

8 more replies
Relevance 95.12%

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.


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

Hi there,

I would really appreciate some help with a problem I am having in Excel 2010.

I have a workbook which is used to track trainings within our organization. It is a file which inludes filter macros which are liked to buttons at the top of the screen (for instance a button to show all completed trainings and one to show all failed trainings etc.). There is also a show all button used to clear filters and each button first runs a command to clear all filters before sorting.

This file has worked very well for more than a year, but is suddenly showing an error: when the data is filtered within a view, especially if it is filtered by more than one criteria (for instance filtered by country, and by name), instead of clearing the filters and applying a new filter I receive this error:

Run-time error '91': Object variable or With block variable not set

Debugging shows the code in bold highlighted. I have pasted the entire code:

Sub WL_Heidelberg()
' WL_Heidelberg Macro
'clear any filters
On Error Resume Next
On Error GoTo 0
ActiveSheet.Range("Training_Requests").AutoFilter Field:=16, Criteria1:= _
"WL Heidelberg"
ActiveWorkbook.Worksheets("Training Requests").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Training Requests").AutoFilter.Sort.SortFields.Add _
Key:=Range("H2:H1842"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNorma... Read more

Answer:Solved: Excel 2010: Run-time error '91' on clearing auto filters

12 more replies
Relevance 77.9%

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

Moving between UK and Europe there is a 1 hour time difference. Previous phones get the local time from the network - not the 640!!!
Stuck on UK time is quite embarrassing turning up for a meeting 1 hour late.
Have checked the phone settings - automatically detect is selected in Time / Date area.
Anyone able to help?

More replies
Relevance 73.8%

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

I'm having trouble creating a macro to insert the current date (static).

Here's what I'm doing:
View > Macros > Record
Name: "Date"
Store macro in: keyboard Shortcuts
Click on keyboard picture
Insert: CRTL+D & click Assign
Record: I have tried all the following:

(/@ "mm,dd,yy")

Then Stop Recording

When I hit CRTL+D I get exactly the code I put in instead of the date "02/12/13"

What code should I be inserting to get today's date?

Thanks, Mike

Answer:Create a Date macro Word 2010

Welcome to the Seven Forums.

start recording
select insert (upper left)
select date & time (upper right)
select the format that you want
click OK to exit the dialog box
stop recording

4 more replies
Relevance 72.57%

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.


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

With due respect and credit to DerbyDad03 whose earlier post gave me the code below; is there any way to have the new worksheets created from a variable length field e.g. a persons name in a list or a field of fixed length other than a date as the script below uses?Sub CreateMonthlySheets()Dim lastRow, mMonth, tstDate1, tstDate2, shtName, nxtRowOn Error Resume Next'Turn off ScreenUpdatingApplication.ScreenUpdating = False'Make a copy of the data sheet and sort by date Sheets("Sheet1").Copy After:=Sheets(1) Sheets(2).Name = "SortTemp" With Sheets("SortTemp") lastRow = .Cells(Rows.Count, 1).End(xlUp).Row Rows("2:" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending 'Using SortTemp Sheet, create monthly sheets by'testing Month and Year values in Column A'Loop through dates For Each mMonth In .Range("A2:A" & lastRow) tstDate1 = Month(mMonth) & Year(mMonth) tstDate2 = Month(mMonth.Offset(-1, 0)) & Year(mMonth.Offset(-1, 0)) 'If Month and Year are different than cell above, create new sheet If tstDate1 <> tstDate2 Then ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count) 'Name the sheet based on the Month and Year ActiveSheet.Name = MonthName(Month(mMonth)) & " " & Year(mMonth)'Copy Column Widths and Header Row .Rows(1).Copy ActiveSheet.Rows(1).PasteSpecial Paste:=8 'ColumnWidth ActiveSheet.Rows(1).PasteSpecial 'Data... Read more

Answer:Excel Macro to create new worksheet in Excel 2010

I created a quick macro to help you out. This macro creates a new sheet and names it using the contents of cell "A1".Sub CreateNewSheet()
Dim NewSheet
Dim SheetName As String

SheetName = Worksheets("Sheet1").Cells(1, "A").Value
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.Name = SheetName

End Sub
Law if Logical Argument: Anything is possible if you don't know what you're talking about.

4 more replies
Relevance 71.75%

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 71.34%
Question: Excel auto date.


Is there any way to get Excel, to automatically insert the date into the first cell in column A everytime a new row is inserted?

Answer:Excel auto date.

13 more replies
Relevance 71.34%
Question: Excel – Auto date


I have the first letters of the days on a row (eg m,t,w,t,f,s,s.) is there any way I can have this cell, row or column to be highlighted automatically upon opening the spread sheet to select todays day/date?


Answer:Excel – Auto date

9 more replies
Relevance 71.34%

Hi,I need to calculate the difference in working days / hours between 2 date / time stamps.Some simple example data which spans a weekend.01/07/2016 18:0004/07/2016 09:30(Working hours are 08:30-17:00 Monday - Friday)In this example the first date / time stamp is when an email was recieved (out of hours) and the second is when the email was replied to (1 hour into workiing hours) So the formula should return 00:01:00:00 (dd:hh:mm:ss)Thank you for your help!!

Answer:Calculating working time between 2 date/time stamps in Excel

It's a rather lengthy formula and needs explanation, so see here: you have further questions, please ask.MIKE

14 more replies
Relevance 70.52%

I have a spreadsheet with 3 columns, (Date, Vendor, and Amount) A1 is "date", B1 is "vendor", and c1 is "amount"
I am trying to set it up so that after someone enters in the information (a date, a vendor name and an amount) excel will automatically sort it by the date rather than having to manually do that everytime something is entered. I have tried some macros off the web but nothing works. It is the 2nd spreadsheet in the actual file if it makes a difference. Does anyone know how to do this, I cannot get it to work. Thanks so much.

Answer:Need help auto date sorting in Excel 03

6 more replies
Relevance 70.52%

I want to have a "code" change a date for a followup field.for instanceDate - 12/9/2015Code - C1 it automatically adds 1 day to the "followup" fieldCode - C3 adds 7 daysetccan anyone help with this?

Answer:Auto update date in excel

i dont understand what you mean, can you please explain with a lot more detail. Please remember we cannot see your workbook so have no idea how it looks or what the fields are. Code - C1 Code - C3no idea what you mean by this......

3 more replies
Relevance 70.52%

So, I'm trying to create a database, and I want the sheet to autgen the date, but only when it's accessed. For example, if someone accesses it today, it gives today's date in the cell. Then, if the next use of the sheet isn't for 5 days or so, it will give the appropriate date, not a sequential one.

Answer:Excel Date Auto Generate

6 more replies
Relevance 70.52%


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:

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

2 more replies
Relevance 70.52%

I have a spreadsheat that I periodically update, and in the lower left hand corner I manually type the date that the information is updated. Is there a macro or such that will automatically update the date when the spreadhseat is opened?

Answer:Auto update the date in Excel

Do you want the macro to change the date when the workbook is opened or everytime it is saved?

There are two events you can choose from to place the macro. Both events are in the workbook Module. With the Workbook open, press ALT + F11 to bring up the Visual Basic editor. On the left side of the editor window you will see your project explorer. Find the item called ThisWorkbook and double click it. This will bring up a blank workbook module. Just copy and paste either of the two code modules below. Just change the destination location of the date which I have made "A33" in the code. The first code is for the Before_Save event and the second is for the Workbook_Open event.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A33").value = Date
End Sub


Private Sub Workbook_Open()
Range("A33").value = Date

End Sub


2 more replies
Relevance 70.52%

I have a spreadsheet where I need a cell to calculate the date (from a cell in column B) plus 14 days to get a due date, but leave the due date cell blank if there is nothing yet entered into column B. What formula should I use for this?

Answer:I need an If/then formula to add a date in excel 2010

Dates can be added just like numbers. Add 1, you've added a day; add 14, you've added 2 weeks.An IF statement can perform a calculation IF the logical_test argument is TRUE:=IF(B1="","",B1+14)If B1 is equal to nothing, return nothing, otherwise return B1+14.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 70.52%

Hi everyoneThis might be an easy one for whoever knows itI have a number 8202014I need to format it as a date to show 08/20/14And the formula should also work for 10202014 to show 10/20/14I cant make a formula with left, mid, right that works correctly.Thanks in advance!!!

Answer:Formating to a date Excel 2010

If you know that the cell will only contain 7 or 8 digits, you don't need the second IF clause. The logical_test will either be TRUE for 7 digits or FALSE for 8, so a single IF function is all that you need.This version is shorter and slightly more efficient:=IF(LEN(E10)=7,DATE(RIGHT(E10,4),LEFT(E10,1),MID(E10,2,2)),DATE(RIGHT(E10,4),LEFT(E10,2),MID(E10,3,2)))Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 70.52%

I have a button on a spread sheet that I want an operator to click on at the start of the day and have it make a new sheet with the current day as the name of the new sheet. Sheets("04-01-10").Select Sheets("04-01-10").Copy After:=Sheets(2) ActiveSheet.Move _ After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count) ActiveSheet.Name = "Today()"That is my code thats tied to the macro button. I tried the change the activesheet.Name without the "" but all i get is errors. Please help.

Answer:Create a new sheet in excel named by date

Well, there are a couple of issues...First, Today() isn't available in VBA. It's "equivalent" is Date. Not Date(), but just DateHowever, if you tried ActiveSheet.Name = Date, you'd get errors because Date would return something like this:7/1/2010Since you can't have / in a sheet name, that would throw up an error.You could get away with this:ActiveSheet.Name = Month(Date) & "-" & Day(Date) & "-" & Year(Date)

4 more replies
Relevance 70.11%

I need help writing a formula to subtract the end time of an event from the start time of the event. I have tried searching functions by asking for a formula to "subtract end time from start time" and variations on this, but keep coming up with TIME(hour,minute,second) or HOUR as the option under "select a function".  I am working in Excel 2007, and I have my times listed as "8:30:00 AM" and "1:00:00 PM" which is the time format as provided by Excel.  Here is an example of my formula:(cell A1 reads) 8:30:00 AM(cell A2 reads) 1:00:00 PMFormula:  =(A1-A2)   Answer when requested as time: ########################################Answer when requested as number:  -0.19It seems so simple, but I am so frustrated!  I need it to build an excel spreadsheet for my boss.Thank you!

Answer:Create Excel Formula to subtract end time from start time

Quote from: LadyGlenProd on June 08, 2010, 02:57:36 PMI need help writing a formula to subtract the end time of an event from the start time of the event. I have tried searching functions by asking for a formula to "subtract end time from start time" and variations on this, but keep coming up with TIME(hour,minute,second) or HOUR as the option under "select a function".  I am working in Excel 2007, and I have my times listed as "8:30:00 AM" and "1:00:00 PM" which is the time format as provided by Excel.  Here is an example of my formula:(cell A1 reads) 8:30:00 AM(cell A2 reads) 1:00:00 PMFormula:  =(A1-A2)   Answer when requested as time: ########################################Answer when requested as number:  -0.19Formula:  =ABS(A1-A2)  Format:  Formula as Time, in type 13:30, i.e. no AM or PM(Formula cell):  4:30

3 more replies
Relevance 69.7%

I want to just have a date as formated like 2/3/2001... Excel 2003 changes always changes this to something like 3rd Feb. How do I disable the auto date correct?

Answer:excel disable auto date change.

Format the cell as text or if you want you can format the cell with the correct date format that you choose.


1 more replies
Relevance 69.7%

Is it possible to have the month say April in cell A1 then in A2 have Sat 1st then auto fill down column A with day date so in A3 would be Sun 2nd ect.

More replies
Relevance 69.7%


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

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

Hello,I sort of got stuck with a very tedious Excel tracker that tracks when people are due for their vaccinations.I know that there are ways to set up formulas in Excel 2007 but I am pretty ignorant in the whole process. I humbly request your help.Here is a gist of my Excel tracker:Column A=Name | Column B=Job Title | Column C=Due DateAnd then the rows fill in accordingly.So, let's say I had this:John Smith | Janitor | 09/15/2010Could I somehow synch those three rows up with my Outlook contacts list and send a formatted email to them? Could I also set it up to CC me?I would want something like, " Hello, if you are seeing this then you are due for your annual vaccination. Please complete your vaccination as soon as possible. Thank you."Thanks for your help.

Answer:Excel Due date tracker-- Auto email when due?

Hi,I can offer some help with formulas.If you have the following arrangement A B C D E F
1 Name Position Vacc. Due Status Rem. sent Vacc. Date
2 Smith J Janitor 15-Sep-10 Due 15-Sep-10
3 Smith K Clerk 10-Sep-10 Done 10-Sep-10 12-Sep-10
You can start by getting a warning when a vaccination is due.In Cell D2 enter this formula:=IF(C2-TODAY()<8,IF(F2="","Due","Done"),"")This tests to see if the due date is 1 week or less away from today's date, using "IF date difference is less than 8 (days)" : IF(C2-TODAY()<8For a longer warning increase the 8 in the formulaThe formula then uses a second IF to see if the vaccination date cell has been completed : IF(F2="","Due","Done"). This returns "Due" if there is nothing in the vaccination date cell, else it returns done.To avoid "Due" showing up when there is no date in cell C2 you can add an extra test for an empty cell:=IF(C2="","",IF(C2-TODAY()<8,IF(F2="","Due","Done"),""))Now to improve the visibility of vaccinations due, you can use 'Conditional formtting' which changes the appearance of a cell based on values in that (or other) cells.Select cell D2.From the Ribbon select Home - Styles - Conditional Formatting, From the drop down select 'Manage Rules' and select 'New Rule'In the dialog box select the last item in the list 'Use a formula to determine which cells to format'Enter this formula in the box =IF(D2="Due",TRUE,FALSE)Note that TRUE and FALSE do not have double quotes - they are Excel's logical values true and fa... Read more

2 more replies
Relevance 69.7%

Where to start?

I want to be able to click on a blank cell, and when I click on it I want it to add todays date without having the user go through the formulas. If there is a date on there from the previous time it should change it to todays date.

It would be nice to have a default words in the cell example-
"Click here to insert date"

I'm kind of fimiliar with VB and had a semester of C++ which doesn't mean anything other than I can follow along.

Any help and suggestions welcome

Answer:Auto Date on cells Excel 2000

Can you just put =now() into the cell? That gives today's date when you format the cell to show just the date (and not the time). It will always show today's date, no matter what date it is today.

3 more replies
Relevance 69.7%

I have an excel spreadsheet with a date column. The column format is *3/14/2001. I enter 08/01/2011. The formula bar reads 8/1/2011. The cell reads 40756. If I put the cursor in the formula bar the cell reads 8/1/2011. Place the cursor any place else cell revers to 40756. How can I get the date to display as a date in the cell.

Answer:excel 2010 date format problem

One of your options is messed up:Click on the Office Button in the upper left cornerClick on Excel Options, down the bottom of pop upClick on AdvancedScroll down to the section that reads: Display Options for this Worksheet:UN-Check the box that read: Show Formulas instead of their calculated results.Click OKThat should correct your problem.MIKE

3 more replies
Relevance 69.7%

I have a date tab with dates (Column A "dd/mm/yyyy", Column B "MMM-yy" and Column C "yyyy-yy" [financial year]).I work in a hospital. I have a list of surgeries with the date ("dd/mm/yyyy") they were performed. I have a Vlookup to obtain the "MMM-yy" data (for my monthly pivot tables) as follows:=IF(ISNA(VLOOKUP($B39,Dates,2,0)),"",VLOOKUP($B39,Dates,2,0))I came across an error in my source tab - all dates in March 2017 had "MAR-16" in Column B. I changed these, manually, to "'MAR-17".The Vlookup continues to work every single month but for MAR-17.When formatted as General (as all the others are), it returns "42795". When I change it to Custom format "mmm-yy" (which none of the others, which work fine, are), it returns "Mar-17" but in the pivot table comes up as "1-Mar-2017" whereas all the others are "FEB-17" etc).Any help would be great.Thanks in advance!

Answer:MS Excel 2010 Vlookup Date Problem

I'm not near Excel right now to try anything, but my guess is that all the working "dates" are not actual dates as far as Excel is concerned. My guess is that they are actually text, which may explain why they don't show up in your pivot table with the "day" portion or display the serial number like the Mar-17 cells do.Excel can be weird when it comes to dates/text, especially if the "dates" were imported from another source. They often come over as text and is can be troublesome to convert them to dates that Excel will recognize. In your case, you may have the opposite problem. I think you have to change your Mar-17 to Text in order for your VLOOKUP to work like the others. That can also be troublesome because Excel will often be reluctant to give up a Date format once it is set. You could try adding a single quote before a few of your Mar-17 entries to force them to Text and see how Excel handles them.Like I said...just a guess.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 69.7%


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?



Answer:Solved: Excel 2010 - date Calculations

use networkdays()


3 more replies
Relevance 69.7%

Hello there, I have been looking a while now without much luck. It may be that I don't quite know the proper search terms but my problem is this:the problem concerns apartments, their occupancy, and their last remodel.I am looking to create a nested function that will return "Need To Remodel" as the result if 'F14' is is unoccupied and was last remodeled before 2005 ('G14'). Otherwise, the function should return "No Change" as the result.Column F has yes/no values concerning whether it is occupiedColumn G has a date (2004,2008,2001, etc) giving its last remodel Any help would be greatly appreciated

Answer:Excel 2010: IF/AND nested function w/ a date

re: Column G has a date (2004,2008,2001, etc) 2004, 2008, 2001 are not dates. If that is all that is in the cell, it's just a number according to Excel.=IF(AND(F14="No", G14<2005), "Need To Remodel", "No Change")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

9 more replies
Relevance 69.7%

All my Excel 2010 files on Win 7 64 bits have had their date formats changed from yy-mm-dd to yyyy-mm-dd; formats that were previously yyyy-mm-dd have been left unchanged.This is very time consuming as I have to do format cells changes on every field of every tab of every file affected; because of the space factor, I need to use the shorter format.I have woken up after spending hours on manual format cells changes and have thought of checking if my short date format in regional date formats had been changed...sure enough; I have made the correction and everything is back to normal.The change must have been made by Windows update between 13/3/22 and 13/3/25; the only time I change formats is when I install a new version of Office (October 2010 in my case).

Answer:excel 2010 loses date format

Since everything is back to normal, I assume this is just an FYI, correct? I didn't want to leave your post as "Unanswered" so I am posting this response.DerbyDad03Office Forum ModeratorClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 69.7%

I am trying to keep track of due dates on my spreadsheets. I have some dates that expire annually and the others biennially.

Answer:how can i track a due date on ms excel 2010 spreadsheet

You'll need to explain what you mean by "keep track of due dates".For example, are you asking to have them turn a specific color when they get within a month of coming due? Are you asking for pop up messages when a due date is imminent?We need details, my friend, details.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more replies
Relevance 69.7%

in excel when i type a date ie..06/10 it shows as 9/1/1901 i have tried changing the format cell but that doesnt fix it. is there another format or formula i need to find to fix this

Answer:excel 2010 help needed for date format

Where are you located, US or Europe/Asia?Is your default date: 12/31/2011 Month Day Year ?or 31/12/2011 Day Month Year ?I do not know how you are getting 9/1/1901 from entering 06/10,but you can try a Custom Format and see how that works.Custom FormatRight click on the cell with your DateSelect Format CellsSelect Custom, at the bottom of the popup windowIn the small input box, under the word Type:delete what ever is in the box and enter:MM/DD for Month Day type of dateorDD/MM for Day Month type of dateClick OKAs long as your cell contained an actual date then you should see it change.MIKE

2 more replies
Relevance 69.7%

Excel 2010 IF stmt based on DATE value: I have a simple employee expense report that calculates mileage in cell D12 based on the date in cell B12. My formulas is as follows: =IF($B12>=DATE(2011,1,7),$D12*0.555,$D12*0.51). All mileage incurred equal to or greater than 07/01/11 should be calculated at $0.555 cents while all else should be calculated at $0.51 cents. I believe it's as simple as Excel not recognizing my DATE(2011,1,7), as the date 07/01/11... Help?

Answer:Excel 2010 IF stmt based on DATE value

You are right, Excel will not recognize DATE(2011,1,7) as 07/01/2011 sinceDATE(2011,1,7) means January 1, not July 7.The syntax is: DATE(year,month,day)=DATE(2011,1,7)=DATEVALUE("1/7/2011") will return TRUE.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 69.7%

cell A1 has military time, 04:25:00condition if the value of time in A1 is between 07:00:00 and 15:00:00, then B1 should display "a"; if the value of time in A1 is between 15:00:00 and 23:00:00, then B1 should display "b";if the value of time in A1 is between 23:00:00 and 07:00:00, then B1 should display "c";

Answer:how to create nested if function for date and military time

Try this and see how it works:=IF(OR(A1<TIME(7,0,0),A1>=TIME(23,0,0)),"C",IF(AND(A1>=TIME(7,0,0),A1<TIME(15,0,0)),"A",IF(AND(A1>=TIME(15,0,0),A1<TIME(23,0,0)),"B")))EDIT:Hers is an updated formula:=IF(A1="","",IF(OR(A1<TIME(7,0,0),A1>=TIME(23,0,0)),"C",IF(AND(A1>=TIME(7,0,0),A1<TIME(15,0,0)),"A",IF(AND(A1>=TIME(15,0,0),A1<TIME(23,0,0)),"B"))))If A1 is empty, this will blank the cell.MIKE edited by mmcconaghy

4 more replies
Relevance 69.29%

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

More replies
Relevance 69.29%

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

Hello Everyone,

I am a beginner at using vba and I am trying to use excel to send an email automatically before 5 days of due date. I have a an excel sheet of bank loan repayment but every loan due at a different date.

If possible i would like to use an email template that excel would place the values specific payment details and their due date and then send the email to the email address on the specific cell.

I have attached my excel sheet format for your referance.
Thank you so much in advanced

Nilesh Jagtap

Answer:Excel - Trigger Auto Email Depending on Due Date

Welcome to TSG niljag!

Working on one of these right now at this TSG thread . Practice1.xls in Post #4 is lastest. See if you can adapt to similar. I didn't look at your attachment.

There are many of these(excel emailers) in TSG. Search "excel email" and find one you like. When you get to an issue let us know and we'll try and help.

1 more replies
Relevance 69.29%

Hello Everyone,

I am a beginner at using vba and I am trying to use excel to send an email automatically depending on a past due date. I have a an excel sheet that has over 200 companies and we require an annual report but everyone report is due at a different date. I am using conditional formating to change the color of the text depending on how long it has been since we have received the last report. If the last report is 366 days since it has been submitted then I would like an automatic email to be sent.
For conditional formatting i used this "=(TODAY()-365)"

For email i am using;

Sub Send_Email_Using_VBA()
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
Email_Subject = "Trying to send email using VBA"
Email_Send_From = "[email protected]"
Email_Send_To = "[email protected]"
Email_Cc = "[email protected]"
Email_Bcc = ""
Email_Body = "Your annual report is Due"
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
End With
If Err.Description <> "" Then MsgBox Err.Description
End Sub

If possible i would like to use an email template that excel would place the values specific to the co... Read more

Answer:Excel - Trigger Auto Email Depending on Date

Hi welcome to the forum.

You already have the send_email_using_vba, weel you will need something that checks all the cells where the due date is x days old and call this macro

Sub CheckSomething()

<< here you will have to put the code that checks each rows in the
<< date column and if the condition is met then call the send email macro

End Sub

Since I have no idea of your sheet's layout you will have to do your own homework

I suggest you also tell us what version of Excel you're using,

1 more replies
Relevance 69.29%

How can I put auto filled Cell like transaction date for any entry in Cell if Cell in same raw has been edited? please I need Help thanks Guys message edited by calemera

More replies
Relevance 69.29%

Dear Friends,

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

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

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

Awaiting early reverts.



More replies
Relevance 68.88%


I have excel file which has a huge data for projects and every day I'm going to add data a minimum of 10 and I have to use scroll down tool. It is possible to create Add button which will bring focus to the next empty column.

Any help is much appreciated.

Best regards,

Answer:Create Add button in excel 2010

the control key and the End key together will take you to the bottom row but the righthand column of used data
if you click on the column you want to start entering data
and then use the control key + the down arrow key will take you to the last entry in that column - MAY NOT be the bottom of the spreadsheet though - not sure how your data is structured

otherwise a macro could be written and added to a button or just run

3 more replies
Relevance 68.88%

I'm trying to create a formula that will input the maturity date of a particular loan. The spreadsheet contains the interest (fixed rate), the monthly payments, and the current balance of the loan (see below).

The current date will be displayed at the top in a separate cell that we can use to base the maturity date off of, but I guess I just don't understand how to do the math to begin with.

Eventually, I'd like to add a separate sheet with the amortization schedule which could easily just be referenced on this sheet instead of doing all the math here, but if I can understand the math first, I can take it from there.

Any ideas? Thanks in advance.

Answer:Excel 2010: Loan Interest End Date Calculation

Hi, I was just curious to see how many Excel posts have gone unanserede and cam accross your post.

I'm not in to economics or finacila calculations, but Excel does have a function named EDATE()
description and syntax is described in the link below:

Amorization table in ExceL: function excel 2010

Maybe this will help you along.

1 more replies
Relevance 68.88%

I have a simple spreadsheet TBH for which two columns of data have date fields:

End date and Reminder date

I would like to set a conditional format using the icon sets for the reminder date as follows:

1) If the reminder date is less than 90 but greater than 60 days of the end date then show me a green icon

2) If the reminder date is less than 60 but greater than 30 days of the end date then show me a amber icon

2) If the reminder date is 30 days or less than end date then show me a red icon

I have come across ways of using today and dates in advance but not before...please can someone help me.?

Answer:Conditional Formatting for a date range in Excel 2010


To use the icon set you'll need to calculate the differences in a new column in the spreadsheet and then apply the conditional formatting to the values in that new column. So if your Due dates are a Col A and your reminder dates are in Col B in Col C use =A2-B2 and drag this down to the end of the list. Then apply the conditional format to Col C.

Here is an example spreadsheet.

1 more replies
Relevance 68.47%

I have a HP PSC 2410xi photosmart all-in-one printer/scanner/copier. It used to usually synchronize automatically with the time and date on PC since they're connected via the USB cable. Right now I have to manually configure date/time. How do I make it automatic again?

More replies
Relevance 68.47%

I want to create a template for invoicing that will automatically give me the next invoice number to use when I create a new invoice. Can anyone tell me how I create a automatic number for invoicing? Or where can I can find information regarding this matter.

Answer:EXCEL: Create a auto number for invoicing

8 more replies
Relevance 68.47%

Dear All,

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

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

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

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

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

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

Hi there, welcome to the board!

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

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

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

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

Private Sub Worksheet_Change(ByVal Target As Range)

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

1 more replies
Relevance 68.47%

Hi, I'm in the military and I have a lot of guys under me. I'm creating (or trying to) a excel spreadsheet to capture the key requirements and ensure I do them the best service.Microsoft Office Excel 2007There are a number of columns with various dates as achieved by the individual. I would like these columns to fill colour change at certain time posts;Green = anything upto 3 months before the cell dateYellow = 3 months before the cell dateRed = On that cell date and anything pastA one off column, date of birth, anyone under the age of 18 colour fill pink. Any one 18 and over no fill.Any and all help would be appreciated - thank you in advance

Answer:Excel; cell auto fill colour change by date

So, if I understand you correctly, we need only concern ourselves with Column B the Annual fitness test Date. Correct?Column C has not impact on what you want to do?If yes, then try this with your data as shown:Conditional Formatting 2007There are Three Rules we will need to applyand they must be in the correct order.First:1) Select your cell or range of cells, IE B2:B42) 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($B2,TODAY(),"m")<96) Click on the Format button7) Select the Fill Tab8) Select a Green color9) Click OK10) Click OKSecond:1) Select your cell or range of cells, IE B2:B42) 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($B2,TODAY(),"m")>=96) Click on the Format button7) Select the Fill Tab8) Select a Yellow color9) Click OK10) Click OKThird:1) Select your cell or range of cells, IE B2:B42) 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($B2,TODAY(),"m")>=126) Click on the Format button7) Select the Fill Tab8) Select a Red color9) Click OK10) Click OKSee how that works.MIKE

21 more replies
Relevance 68.47%

HELP!! Does anyone know HOW to customzie the Date and Time stamp on emails in Outlook 2010? I want each and every single email to show up in my Inbox (whether read or un-read/new) as the DATE, i.e. 6/19/2012 9:24am I do NOT want the words "Today" or "Yesterday" or "Tues" - AND - I want the entire DATE to appear and the TIME to appear on every message. I find and sort emails by time and date. I used to have that as a default in Outlook 2003. How do I change the default in 2010 to be what I want?


Answer:HELP!!! OUTLOOK 2010 - Date&Time stamp on emails - can I customize?

9 more replies
Relevance 68.47%

Hello guys,

I have a report which has (User, Office, Problem, Date time open, Date Time Close, and Total Date time of job.) TotalDateTime calcultates the time from when it was open to when it closes.

The report shows the total of all jobs so i can print it out. At the bottom of the report i want it to tell me the total time of all the TotoalDateTime records, i.e:

Joe Bloggs Open-11/06/2011 10:30:00 Close-12/06/2011 11:25:00 Total- 1 Day, 55 Mintutes
Jack Black Open-11/06/2011 10:30:00 Close-12/06/2011 11:25:00 Total- 1 Day, 55 Mintutes

TotalOverPeriod - 2 Days, 1 Hour, 50 Mintues

I cant seem to make it total up...Anyone know how to do this?

Thank you

Answer:Solved: Access 2010 Total Time and Date Values

16 more replies
Relevance 68.47%


I am creating histogramm for statistics course. How can I please add two adjacent Y-axes to histogramm?

Typical example image of histogram is attached.


Answer:How to create two adjacent Y-axes in Excel 2010

Hello Dodge1, welcome to Seven Forums.

Looks to me like the answer in this thread might do the trick:
How can I create a Dual Y axis in Excel? - Yahoo! Answers

Good luck! Let us know if it works.

1 more replies
Relevance 68.06%

I haven't been able to quite get my functions right on this one and am hoping for some guru help.

I have a spreadsheet with dates in Column A (Contract Received) and Column C (Contract Filed). Not every cell has a date in one or both fields, as it is a tracking tool for work in progress. However, a contract must be Received before it can be Filed, so C will (er,should) never be blank if A is populated. I want to know the number of days lapsed between date Received and date Filed, and if date Filed is null, then number of days from Received to today's date. If the contract has not been Received, then no calculation should be performed. In spite of requests not to, users sometimes put text notes in these fields, so would be nice to ignore text. At bottom of column, I need to get an average of the time lapse on all filings to track productivity. This doesn't sound so difficult to me, but somehow I haven't gotten it quite right in the last two hours and it's time to ask an expert. Thanks in advance for your help!

- k

Answer:Excel 2010 Date Calc Formula, Ignore Blanks

Hi k, welcome to TSG.

Try this and let us know if it gets you close to what you need:


It basically starts with column A and determines whether there's a number there (a date, as opposed to text or blank). If not, it doesn't do anything, but if so, then it does the same thing with column C. If C has a number (date) then it subtracts A from C. If not, it subtracts today's date from A.

Let me know if I've misunderstood the question.

1 more replies
Relevance 68.06%

I have a chart whose title I would like to read "Between (Date 1) and (Date 2)" The Date 1 field, AD4, and the Date 2 field, AF4, changes depending on what date range I want to use for data. I looked on a previous post,, which led me to put in the formula bar: ="Between " &$AD$4 "And " &$AF$4. Excel gave me an error, so for now I'm just trying: ="Between " %$AD$4. When I do this, the field displays the date as a serial number (ex. 40544) instead of the date. Any ideas on how to fix this and be able to put both the before and after in the title?

I'm running Excel 2010 on Windows XP.


Answer:Solved: Excel 2010 Dynamic Chart Title Date

What happens if you link the title to a cell containing:

="For "&TEXT(AD4,"dd-mm-yyyy")


NB: this response is to your pre-edit question.

3 more replies
Relevance 68.06%


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.

More replies
Relevance 68.06%

Yo folks,

Annoying little thing in excel,
I want to show the date exactly like this: 00-00-0000 (08-03-2012)(dd-mm-yyyy)
Instead excel removes the zero's like this: 0-0-0000 (8-3-2012) (d-m-yyyy)

Is it possible to keep the zero's?

Thanks in advanced,

Answer:Excel 2010 question: Date 00-00-0000 (08-03-2012)(dd-mm-yyyy)

what i think you want is to format the cells with a custom format...

Use dd/mm/yyyy for the Type

edit: use dd-mm-yyyy for the Type

2 more replies
Relevance 67.65%

I have some data I downloaded from a recorder in the form of a *.csv file. Basically, the recorder logged the electric load seen at a power plant in 15 minute intervals from last June up until yesterday. The time and date on the recorder were off by 7106382 Seconds (or 82.2 days).

When I open the *.csv file in Excel 2002, the time and date are together in a cell A12 as 2/15/2007 2:45:00 PM. The cell has a custom format of m/d/yyyy h:mm.

I want to add 7106382 seconds to the time in the cell, and have Excel give me the new date and the new time.

Is it possible? How?

Thanks for the help.

Answer:Excel - Date & Time

Welcome to the forum.

You can have the incorrect date added plus 82.24979167 and it should give you the correct date and time.

2 more replies
Relevance 67.65%

I have buy a new Y570 a few day .1. Y570 sometimes can't boot up when disconnect  AC adapter to power on Y570 (only has power on light a few second after shut down) ,certainly connecting AC adapter power is normal. 2.bios date & time MUST auto reset to factory default after power off Y570 and disconnect AC power around 10 Minutes but other bios config NO CHANGE. please help me how to fix this problem THANKS ALL Bios verison : 2.08 

Answer:Y570 somtimes can't power on and bios date & time auto reset

hi xrgb3can you try this trick first?- remove the battery, unplug the ac power, press power button for 30-40 seconds, replace the battery and check if it works.

1 more replies
Relevance 67.65%

Background Explanation: I have one file with multiple sub-directories produced from a third party software source that will automatically load to Excel. The sub-directories typically contain specific results by department for example. Instead sub-directories I would like the results listed...

Actual Macro Needed: Auto create one workbook with multiple excel worksheets.



More replies
Relevance 67.65%

Hi again,

This one should be much easier than my last issue ...

I'm writing excel macros and everything is going great except that when i tell my macro to save a particular file i need it to add that day's date (the current date) to the filename...

Any takers?


Oh yeah, Merry Christmas and Happy Holidays to everyone at Tech Support Guys!!

Answer:Solved: Excel 2003 VBA code to Auto Date file names?

7 more replies
Relevance 67.65%

we use outlook to view messages in the military. these messages are most likely refrenced by their date time group. in the message ( body of the email ) it will state the DTG.. example DTG: 291323Z Apr 10

i would like to make a field in my view so i can have the messages displaed by DTG. is this possible? if so how?


More replies
Relevance 67.65%

I need to change the color of cells F6:F39 if F5="Actual". Can this be done? If yes. how? Thanks!

Answer:how do i create if statement in 2010 excel to change cell co

Use Conditional Formatting.Select F6:F39Home RibbonConditional FormattingNew RuleUse a Formula...=$F$5="Actual" (Make sure you use the dollar signs as shown)Select your formatClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 67.24%

External data does not automatically update in excel 2010. I have several workbooks linked together to share data (several log workbooks and one master workbook). When I open the master workbook in Excel 2007, the data updates automatically as it should. However, when the same file is opened using Excel 2010, it prompts me to update the data rather than updating it automatically. This wouldn't be a big problem except for the fact that the worksheet that is viewed when the master file is opened is a pivot table. When I select the update, it updates the data in a hidden worksheet but the pivot table does not refresh unless I do it manually. Most of the users that are viewing the master workbook are not familiar with pivot tables and do not understand this. The "Enable all data connections" and "Enable automatic update for all workbook links" are both selected in the External Content settings.

Answer:External data does not auto update in excel 2010

I'm not at all sure abut this, but the only thing that comes to mind areThe DDE settings in the Advance section of the Options menu under the General Headingand the Save External Link Values, also in the Advance section of the Options menu under the When Calculating this Workbook heading.MIKE

2 more replies
Relevance 67.24%

how can I switch on/off auto-hide in excel 2010. while I'm typing the toolbar is hided, but when the mouse is placed on the top of my program it switched on.

Answer:excel 2010 How To Turn Off Auto-Hide Ribbon

Leave the mouse pointer inside the program window.

3 more replies
Relevance 67.24%

I have Microsoft Excel 2010 and I have Windows Vista.I am wanting to create a Spreadsheet which will allow me to keep a record of my weight. I check my weight every 7 days.I was wanting to know how I could create a Spreadsheet that would have a column for the date and another column where I enter the weight.I would like Excel to automatically enter the date, in the date column and automatically advance the date value by 7 days for the next entry.Does anyone know if it is possible to do what I am wanting, and if so, could anyone give me instructions on how to do it please.Thanks in advance.

Answer:Excel 2010 auto adding 7 days to column

Enter a date in A2.In A3 enter=A2+7and fill down as far as needed.

10 more replies
Relevance 67.24%

can sum1 hemp me to do a formula for automatic computation or prediction of date & time in excel.

example: current date & time 9/13/2015 8:58 + 60 hours =

i hope someone can spend time to check and help m on this.. thank you & best regards.


More replies
Relevance 67.24%

I'm working with a table with the following date format:

2003-04-05 00:00:00.0

I need to perform some date functions (count days between dates and such) but I can't work with the data when the blank time stamp is there - "00:00:00.0". The data comes off a mainframe db so the data can not be transformed pre-excel.

I have taken it out manually for a few cells in order to do some testing and it works perfectly. I tried truncating it out but to no avail.

Please Help. Work Urgent.
Edit : While I'm at it, anyone know QuatrroPro?
This table contain 440 000 rows, which is too big for Excel to handle (65 xxx limit). Ideally, I would like to do this from QuattroPro but I'm having the same problem. So now, I'm doing it bit by bit and pasting it back into my QuattroPro Spreadsheet until all 440 000 rows are done. QuattroPro has a row limit of 1 000 000 FYI.


Answer:Excel Date-Time truncate...Please Help

8 more replies
Relevance 67.24%

I have a call tracker that I am trying to automate the input of the date and time. My time is not working correctly and my dates are changed for all entries when the tracker is updated then next day.I uploaded it to my dropbox so you can see exactly whats happening.

Answer:Automating date and time in excel help

I can't access the file right now, but I'll toss this out...If you are using Excel functions such as TODAY() or NOW(), you should be aware that those functions are known as "Volatile Functions"From: Volatile Function is one that causes recalculation of the formula in the cell where it resides every time Excel recalculates. This occurs regardless of whether the precedent data and formulas on which the formula depends have changed, or whether the formula also contains non-volatile functions.In other words, =TODAY() will return the then-current date, based on the OS date, which means it will change every day. = NOW() will update the Time portion every time the sheet does any type of calculation, anywhere on the sheet.The only way that I know of to automate the entry of the Date and Time and have it remain static is to use VBA (a macro) which can write the actual date and time in a cell as opposed to having Excel pull it from the OS via a function.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 67.24%

I have two dates/times that I need to calculate total hours elapsed. For instance 02/03/2011 10:18:27 for one time02/05/2011 19:27:34 for the second timeI need the total hours/minutes elapsedHow would I use excel to calculate this time?

Answer:Calculate in excel with date/time

B - A = XNigelMobo: Asus P7P55D LEOS: Microsoft Windows 7 Professional OEMCPU: Core i5 750 @ 2.67 GHzRAM: Corsair Dominator DHX+ DDR3 1600MH 4GBGPU: Sapphire 4870 D

2 more replies
Relevance 67.24%

I am using excel 2007. I have a column where date and time are present in the format mm/dd/yy hh:mm:ss AM/PM. I need to compare this with timings and based on that need to output string like "NA", "AP", "EU".Sample date/time format is: 8/6/2010 1:31:52 AMAs per the timing given below the above date/time should output as "AP"Condition 08:01:00 AM - 05:00:00 PM NA 05:01:00 PM - 02:00:00 AM AP 02:01:00 AM - 08:00:00 AM EUAny help should be highly appreciated.

Answer:Compare date and time in excel

First: Your subject line is a bit confusing. It says "Compare date and time" yet your post doesn't deal with the date at all. Is it safe to assume that we can ignore the date since you only seem to be comparing times?Second, there seems to be a problem with your time ranges. Your ranges all end "on the hour" i.e. 0 minutes but they all start at 1 minute after the hour.What should the output be for 05:00:32 PM? or 02:00:45 AM? or 08:00:01 AMShouldn't your ranges all end in :59?OK, assuming my assumptions above are correct, try this setup:
1 8/6/2010 1:31:52 AM AP 12:00:00 AM AP
2 2:01:00 AM EU
3 8:01:00 AM NA
4 5:01:00 PM AP
The formula in B1 to return AP is:=VLOOKUP(MID(A1,FIND(".",A1),20)*1,$C$1:$D$4,2,1)How it works:Excel stores Dates and Times as numbers, with the integer portion representing the Date and the decimal portion as representing the Time.e.g. 8/6/2010 1:31:52 AM is stored as 40396.0637962963The MID and FIND functions extract the decimal portion of the date in A1 as a text string.MID(A1,FIND(".",A1),20) = .0637962963 (as Text)Multiplying the string by 1 (or adding 0, etc.) will turn it back into a number.The VLOOKUP will then lookup that "number" in C1:C4 and return the corresponding value from D1:D4. Remember that the Times in C1:C4 are actually stored as numbers, so that is what VLOOKUP will look ... Read more

6 more replies
Relevance 67.24%

Hi, i'm trying to calculate the amount of time between a scheduled date and time and an actual arrival date and time. I have a query that pulls the data and exports it to excel in the following format.

9/8/2004 00:02:33 9/7/2004 23:47:00
9/8/2004 00:06:00 9/3/2004 23:35:00
I need to calculate the amount of time an arrival is late. I'm not sure the best way to do this. Can anyone help???


Answer:date and time interval in excel

14 more replies
Relevance 67.24%

I may have my stupid head on but:Excel displays negative dates as an apparently infinite row of hash marks.I have a spreadsheet that calculates hours worked by deducting start time from finish time - all formatted as hours and minutes xx:xx.It then subtracts standard hours from hours worked to give extra hours worked - also xx:xx. So far so good, but it all goes to pot when the employee works less than standard hours and all I get is ### ad infinitum.Any suggestions for a simple solution - I can use an IF function but need to explain it to non IT literate users (or I can use an IF and hide the cell, I guess)Thanks

Answer:Excel negative time / date

You can try Tools > Options and change to the 1904 date system which I believe supports negative times.The alternative is to use a formula like=MOD(B1-A1,1)which will return a positive time even if A1 > B1 - you could use an extra cell to indicate whether that condition was true.

2 more replies
Relevance 66.83%

Hi, I've got something that keeps going wrong.\
I have a large file which I use at work to register and plan my colleague's tasks and resgiter (outside our regular vacation/sick/abscence, etc.) the different calls they are working on allocating this. But that works, that's not the issue.
I have a sheet named "Medewerkers", yes it's Dutch for Employees of Colleagues.

I extracted and stripped this portion. (June 16: removed this attachment, correct attachment in final post)

On this sheet double clicking in Column A on the name will open a userform to edit this one or if you double click the first empty row below it you get a blank form to enter the data.
Since I regularly ran into problems useing the ....ocx calendar picker I wrote my own date picker and it works. Using son supporting code I found the funtion GetDateInLocalFormat() does it's job.

But now the issue:

The person on the sheet has as starting date 01-03-2012 (in Dutch format this March 1, 2012) and ends August 2012

On my system and at work The date pickers selects it correctly, show is and the userfrom shows it as 01-03-2012 but when I press 'Vastleggen" (Commit) the date is saved as 03-01-2012 which in Dutch January 3, 2012 when I double click again the datpicker shows the default data as January 3, 2012.

The cell's format is dd-mm-yyyy;@ the system's settings are for code 31 which is Dutch and I have pasted screen shost of the settings that show this.

I... Read more

Answer:Solved: Excel (2010) Date format issue dd-mm-yyyy and I can't find the solution!

I changed my code a littel and instead of passing the date in local format I pass it as a serial date and then there is no doubt and the result is correct. But if somebody can offer a better solution, I'm all eyes and ears, and ... maybe it will help others who struggel with date values and the international settings

2 more replies
Relevance 66.83%

I'm using a radio button to make a selection on a spreadsheet but when I use the cell link feature the 1.00 or 2.00 turns white in a light grey cell. I'm trying to make the 1.00 / 2.00 the same color as the cell.Also, I want to run this macro in the loop from row number 6 to 128. Any ideas?

Answer:How do I create an Excel 2010 Macro to change font color?

Do you have a macro already or are you wanting us to create one for you? if you need us to help make one then please provide more detail, what is the purpose of this macro exactly? what is the process you want to follow before the macro is triggered?a lot more information is needed.

3 more replies
Relevance 66.42%


I have been looking at your threads and see that you have covered a similar topic a few years back.

I am looking for away to automatically back-up excel 2010 workbooks into a specific folder, so that when I complete filling in the information sheets for my clients and click on the 'save all' box on the spread-sheet, all the back-up excel workbooks can be found in the same folder. I had some luck with a VBA macro but it keeps making a new folder every time I save. If I add the name of the folder, that I want the workbooks to save in, it doesn't work at all. Any ideas? Is there a shorter route than the VBA macro?


Tech Support Guy System Info Utility version
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz, Intel64 Family 6 Model 42 Stepping 7
Processor Count: 4
RAM: 8080 Mb
Graphics Card: Intel(R) HD Graphics, -1988 Mb
Hard Drives: C: 232 GB (168 GB Free);
Motherboard: Hewlett-Packard, 1495
Antivirus: Sophos Anti-Virus, Enabled and Updated

More replies
Relevance 66.42%

Dear All,

I am new to this forum and not sure if this had already been answered. But here it goes anyway.

I would like to create a vacation form that users can use to fill their vacation requests. This form will have approve or reject buttons. If approved, the data would be auto populated onto an excel spreadhseet. If rejected, the rejection email will be sent back to the user.

Can someone show me how to do this.

Many Thanks for all your help in advance.



More replies
Relevance 66.42%

Excel gurus,

Back again with another difficulty...

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

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

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

P.S. I'm using Excel 2003.

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

9 more replies
Relevance 66.42%

Hey!I cannot find a solution for this and i know there are some smart guys out there :)What i need to do is combining a date and a time in 1 cell (but not adding them)Example:A1: 01/03/2011A2: 14:00what i would need now is having it one cell which would be:A3: 01/03/2011 14:00Thanks a lot in advance!Manuel

Answer:EXCEL Combine Date and Time in 1cell

Try:=A1+A2Provided A1 is formatted as a Date and A2 is formatted as a Time.MIKE

8 more replies
Relevance 66.42%

My DAQ system outputs the time and date for each data point collected in the first column along with the various signals in other columns, and I have at least 4k data points in each column. Time and data is given as shown below.11:54:23:11 - 23 MAY 1011:54:38:11 - 23 MAY 10I am trying to extract the time diference between the two data points by subracting the info in two cells, and obviously Excel does not recognize the info in the cell because of the presence of ":11" after hh:mm:ss info.How would I delete ":11" from the column and convert the given info to a Excel readable time/date format so that info in two cells could be subtracted to get the time difference.Thanks

Answer:Time and Date Format in Excel 2007

Are the Time & Date together in one cell?If the times are in there own cells, then Custom Format as:[h]:mm:ss.00This will allow you to keep the full time.MIKE

7 more replies
Relevance 65.6%

This one has got me. Has anyone figured out a way to do this easily? I can create a new column and extract the right characters and make it work ... but just wondering if there is a quicker way out there!

Thanks in advance for your time/help ...

Answer:Excel: Extract hour ONLY from a date/time field

Ok, got it to work ... but not sure if there is a better way.

I created a 2nd colummn and used the =RIGHT(D107,5) to isolate the time (10:32) from the date/time field (JAN 12,2014 10:32)

I then created another column and used =TIME(HOUR(C107),0,0) to pull out the hour ONLY in 10:00 AM format.

Is there a quicker way out there?

2 more replies
Relevance 65.6%

I'm trying to find an excel formula that would help in finding overlapping count for sessions that have specified starting and ending times..
note: the times and dates are in the same cell for each session as follows: (a sample file is attached as well, please check if the format is not clear here..)

Name Session Start Date Time Session End Date Time
AA 9/22/2016 8:43 9/22/2016 15:43
AA 9/22/2016 8:51 9/22/2016 15:45
BB 9/22/2016 8:58 9/22/2016 15:51
BB 9/22/2016 9:06 9/22/2016 15:56
CC 9/22/2016 9:25 9/22/2016 15:59
CC 9/22/2016 9:40 9/22/2016 18:02
AA 9/22/2016 10:03 9/22/2016 18:09
BB 9/22/2016 10:03 9/22/2016 18:12
CC 9/22/2016 10:08 9/22/2016 18:15
AA 9/22/2016 10:44 9/22/2016 18:21
CC 9/22/2016 10:44 9/22/2016 18:24
BB 9/22/2016 11:00 9/22/2016 18:26
AA 9/22/2016 11:09 9/22/2016 18:33
CC 9/22/2016 11:16 9/22/2016 18:31

Answer:checking overlapping for sessions with time and date excel

can you also provide the expected result from those dates and times
And what you mean by overlapping

The only one thats starts after and ends before a session is the last sesssion which overlaps with the 2nd to last
11:16 to 18:31 overlaps with 11:09 - 18:33 because it ends before and starts after
so thats 2 sessions
will count the number of sessions where the start time is greater or equal and the end time is less or equal
what is the significance of the Name ?

2 more replies
Relevance 65.6%

I have Office 2007 running on a Windows 7 64 bit Toshiba laptop. I am trying to separate the data and time in an Excel file in one column and put the date in one column and the time in another. I need a "how to" procedure to do that.

Answer:Separating date and time in Excel Office 2007

Excel has formatting for Date and Time.
For column A use a Date format, and for column B use a Time format.
To copy the value from column A to column B a function in column B like this should work
= IF(A1="","",A1)

5 more replies
Relevance 65.6%

I have pasted data in to Excel that is in two columns
Col A contains a list of dates, dd/mm/yy; col B times hh/mm/yy. How do I set about calculating and displaying the period in dd/hh/mm/ss that elapsed between col A row B with col B row B, and col A row C with col B row C ?

Answer:Excel 2007: a question about date and time manipulation

Post a picture of a sample worksheet containing sample data so I can fully understand what you need to do.

I will replicate the picture you post and can probably figure it out.

As I understand it, column A contains only something like 13/6/2007 (June 13, 2007). Column B contains hours I guess, but I need a sample to actually look at and ponder.

You want to calculate the total time elapsed between some time on June 13, 2007 in column A and some later time as shown in column B??

Column A contains no reference to hours? Hours are found only in column B?

More info please.

4 more replies
Relevance 64.78%

Hi there,
I came across this thread (now closed)

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

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

Thank you in advance.

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

Hi there,

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


3 more replies
Relevance 64.78%

This is very strange. I created a time tracking system for the company I work for. What they do is enter the time they start each task throughout the day and at the end of the week it all gets imported via VBA into an access database for the manager to review time cards. We had a need for people to be able to edit a record after importing so I set it up to do that. Problem is I found out when Excel sends the "start time" over to Access, it is sending over the time, for the year 1899. If I open Excel on my PC, click on Cell A1 and type 8:15 AM and hit enter, it shows in the bar at the top "8:15 AM". If i store that time in a variable in VBA and format it to show the year associated with that time it comes through as "12/30/1899 8:15 AM". This totally screws up my ability to edit records because when I programmed Excel to edit records it is searching based on a date/time to find the unique record because for John Doe and his time card he will have only one job starting at 8:15 AM on 4/9/12. So it looks up the record by the combination of the date and time. Problem is Excel is not storing a time as "today at this time" so lookups are failing because the year is wrong.

How come if I type in 8:15 AM in Excel it is not stored as 4/9/12 8:15 AM? The date is in a different cell on the sheet so if there is a way I can append the date with the time I guess that is what I will have to do. But as of yet I don't know how to do that.

Here is ... Read more

Answer:Excel date/time problem. Wrong year being displayed

9 more replies
Relevance 64.78%

I'm suffering formula confusion...

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

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

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

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

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

8 more replies
Relevance 64.78%

I imported data from another program into Excel and it is giving me the date and time in this format:10012015 01:40I need it to look like this:10/01/2015 01:40The data is in Cell R72. When I use the formula below, it comes up as 8/15/03 1:40 instead of 10/01/2015 01:40.=DATE(MID(R72,1,4),MID(R72,5,2),MID(R72,7,2))+TIME(MID(R72,10,2),RIGHT(R72,2),0)Any help would be appreciated!message edited by rajasperson

Answer:Convert text string to Date and Time format in Excel

Try this:=DATE(MID(R72,5,4),LEFT(R72,2),MID(R72,3,2))+TIMEVALUE(RIGHT(R72,5))The syntax for the DATE() Function is: =DATE(Year , Month , Day)The Year starts at the 5th Character and is 4 Characters longFor the Month we get the First Two characters from the LEFT() side of the text string.For the Day we start at the 3rd Character and get 2 characters.MIKE edited by mmcconaghy

3 more replies
Relevance 64.78%

I've created an excel worksheet, and I'm trying to track the time that it takes to finish a project. My problem is that I need to see a running (real-time) clock that shows the days elapsed between the time the person starts the project "Start Date" (signified by them entering a start date, month/day/yr), and the time that they finish the project "Completion date." (signified by the person entering an End date, month/day/yr). Once the person completes the project, and enters the "Completion date." The timer needs to stop, and retain the number of elapsed days. It would be nice if the real-time counter is formatted to display in Days, Hours, and minutes. I also want to avoid getting error messages when either cell is empty. Is this something that you can help me with? Thanks.

Answer:How to create a Real-time counter in Excel

Put your Start Date in cell B1Put your End Date in cell D1Put the below formula in F1(This may be a bit of overkill, but you might like it, it will update every time your worksheet updates.)=IF(D1="",DATEDIF(B1,NOW()-(MOD(B1,1)>MOD(NOW(),1)),"ym")&" months, "&DATEDIF(B1,NOW()-(MOD(B1,1)>MOD(NOW(),1)),"md")&" days, ",DATEDIF(B1,D1-(MOD(B1,1)>MOD(D1,1)),"ym")&" months, "&DATEDIF(B1,D1-(MOD(B1,1)>MOD(D1,1)),"md")&" days, ")It's all one long formula, just copy & paste.If you also want the hours & min, the formula gets a lot longer.MIKE

14 more replies
Relevance 64.37%

Guys ! Can You help me ? How can I set auto time in excel ? It's to important to me . guys please help me :( .

More replies