# Excel 2007 :: How To Get Expected Time Of Closure

Feb 19, 2014

How to get the "Expected date and time of closure" in MS Excel 2007

Call No. ABCD
Login Tme 9:30 AM
Agreed SLA (Service Level Agreement) 1hr
Accessibility Type 24hrs
Site Accessibility 24hrs
Accessibility Days All Days
ETC Date ?
ETC Time ?

[Code] ......

## Can No Longer Get LOOKUP To Work As Expected! (2007)

Jun 17, 2009

I have used the LOOKUP formula several times with no problem.

Now -- even in the simplest example -- I can't get it to work as I expect.

Rather than explain, attached is a very simple spreadsheet that shows how the LOOKUP formula is returning a different value than I would expect.

As mentioned on the spreadsheet, the value I would expect to be returned is "euro", not "other sam".

## Excel 2007 :: Calculate Time By Time Periods Splitting Productivity?

Mar 8, 2014

production01.png

I am trying to figure a way to search for a cell that has a specific date and time range. There are several cell titles pending on the activity. I want to find a cell that has a time ** 7:30-15:30 , 15:31-17:30, 17:31-20:30. The end result is to calculate the activity between those time periods based on the data cells.

Example

If the date searched time field ** the activity ranges is 1635 I need to split the time and credit the activity time in the 730-1530 time and the rest on the 15:31-17:30 time

I have been able to do it on a single labor group based on time alone, but when I try to add the DATE to it my numbers go null. Eventually i will need to add 11 labor groups daily for weeks at a time .

Excel 2007

## Excel 2007 :: Calculate Hours From Time?

Jul 20, 2012

I want to calulate time by decimal the problem is anything after 1 am wont work

example start at 12:25 finsh at 1:45 time should show up as 1.33

using excel 2007

## Excel 2007 :: Adding Items To Combobox At Run Time?

Jan 12, 2012

Adding items to combobox at run time

Setup office 2007 win xp pro ie8

Further to my post relating to swaping jpgs

The code below scans from the column on the row of the worksheet till it finds a blank, this seems to work ok as I previously had a msgbox in that displayed the value of n once the loop finished

So I was hoping that the values in the columns on that row would be added to the combobox but for some reason I get a typing missmatch error.

Code:

Dim n As Integer
n = 1
Do While LValue6 "" 'find out how many alternatives there are
LValue6 = FoundISBN.Offset(0, 32 + n).Value
n = n + 1
DataInput.ComboBox1(n) = LValue6 - I was hoping that the value in the cell was added to the combobox
Loop

## Excel 2007 :: Inconsistent Time Scale - X Axis

Mar 8, 2012

Basically I have some performance figures for a fund that I want to make a chart for, the problem is that the first date (the start date of the fund) is 15/09/2011 but every reporting date after that is the end of the month.

So my data looks something like this (not real figures)

15/09/11 20
30/09/11 22
31/10/11 23
30/11/11 25
31/12/11 26
31/01/12 27.5
29/02/12 29

So what I want is a graph where the x axis starts at 15/09/11 but then the next label is 30/09/11 and 31/10/11 etc, while still keeping the scale proportionate to the dates, ie the distance between 15/09/11 and 30/09/11 will be half the distance between 30/09/11 and 31/10/11.

The 2 outcomes I seem to be stuck with is either forcing the graph to start at 31/08/11 and making the y axis cross at 15/09/11, however with this the x axis goes left past the y axis cross point and it just looks dumb, although everything right of the y axis is correct.

Or secondly I get the graph to start at 15/09/11 and set it to label every 1 month and all the labels are 15/09/11, 15/10/11, 15/11/11 etc

I have access to excel 2003 or 2007.

## Excel 2007 :: Removing Duplicates Within Time Range

May 28, 2012

I am using Excel 2007 on Windows 7. I would like to remove duplicates as follows: If column A = column A, column B = column B, and the two rows are within one hour of each other, then remove all duplicates, leaving one instance of the row. As you can see, sometimes the duplicates are right next to each other, and sometimes have rows between them. (Rows 1 & 2, Rows 30 & 32). Even though this sample doesn't show it, sometimes there can be tens of duplicates within an hour, not just two.

Excel 2007
ABCD1EruptionI Can't Stand The Rain (78)3/7/20129:12:55
AM2EruptionI Can't Stand The Rain (78)3/7/20129:10:02
AM3ChicagoJust You 'N' Me (73)3/7/20129:05:59

[Code] .........

## Excel 2007 :: Cells With Time Change Unexpectedly

Oct 7, 2013

I enter a date and time in a cell, e.g. "2013-10-07 12:30", and then I save the workbook.

When I open the Workbook again, the cell contents is "2013-10-07 00:00".

I am currently using Excel 2007. I haven't tested this explicitly with Excel 2013 which is my normal working version, but I think I should have noticed it if it had happened in 2013.

## Excel 2007 :: Data Points - Filtering Date / Time

Mar 15, 2013

I have a set of data points with date and time shown in the picture. I need to take out all the data point that are at the 30 min interval. Meaning I only want to have the data points hourly only. What can I do to remove the :30 minute datas.

I have tried the find option typing In :30 and getting all the points but then have to replace, which I do not want to do. Need to remove them so they do not show up on the trends ( graphs) I made for them.

This is for excel 2007.

Seems I can't get picture up on the ipad, so the data points look like this

1/1/2012 0:00
1/1/2012 0:30
1/1/2012 1:00
1/1/2012 1:30

And so on for an entire year. Just need to remove the ones with the :30

## Excel 2007 :: Dropdown Calendar - Date And Time Picker

Apr 13, 2013

I want a drop down calender to choose date from and found that i can use More Controls> "microsoft Date and Time Picker 6.0 (SP4)" for excel 2007 but it doesn't work with other excel and shows a "x" not recognised in some other machines.

## Excel 2007 :: What Data Type To Use When Multiply Time By Money?

Feb 21, 2014

[h]:mm X accounting (2 d.p) = ????

The two data types i'm using are shown above but i don't know what data type to use as the result.

## Excel 2007 :: Start Timer After Workbook Open For Certain Amount Of Time

Dec 28, 2013

I have the following code below for a timer in a userform. Right now it is configured to start when a button is clicked. I would like to have it automatically start the timer after the workbook is open for more than 5 minutes but so far have been unable to get it to do so. I have tried putting the code in the workbook module but it still won't run. It also has a button to reset the timer if they need more time in the workbook and also a button for them to save and close the workbook if they are finished. I'm running Excel 2007.

VB:
Private Sub CBReset_Click()
Dim T, E, M As Double, S As Double
T = Timer
Do
E = CDbl(Time) * 24 * 60 * 60 - T 'elapsed time in secs
M = AllowedTime - 1 - Int(E / 60)
S = 59 - Round((E / 60 - Int(E / 60)) * 60, 0)

[Code] .....

## Excel 2007 :: Current Time Causing Cell To Change Colour

Jun 4, 2014

I am creating a spreadsheet in Microsoft Excel 2007 which holds information about the airline Emirates which I have created using information from the internet. I am currently creating a sheet which has all their flights in and holds information about the departure time and arrival time of the flight and the type of aircraft ect. I am wondering if there is anyway in being able to have the cell which has the flight number in to change colour (Green or Red) if the flight is in the air or not by using the departure and arrival times already set on the sheet. I am wondering if you possibly need to have a live time on the sheet so that it can work with that .....

## Excel 2007 :: How To Remove Expressions From All Current Time Member Properties

Jun 21, 2009

When I open a spreadsheet I get the following message

Excel found unreadable content in Book_Name. Do you want to recover the contents of this workbook.

All the information I find says:

This issue occurs if the following conditions are true:
The workbook contains a PivotTable that uses key performance indicators (KPIs).
The KPIs are created in the Analysis Services Business Intelligence Development Studio.
One or more of the KPIs have an expression in the Current Time Member property.

and the fix is : To resolve this issue, remove the expressions from all Current Time Member properties.

My issue is - the spreadsheet with Pivot tables is supplied to me. I am using Excel 2007 - how to remove the expressions?

When I do this on another machine same version of excel I have no issue.

## Excel 2007 :: Security Alert - Data Connection Message Every Time?

Dec 20, 2012

I open a spreadsheet or workbook that has links to other workbooks I am notified by the message bar that content has been blocked. I click Options > Enable this content > OK. Even if I don't make any changes I'm prompted to save changes when I close the file. If I hit Yes or if I hit No, I will still be prompted to enable content in the message bar the next time I open the same file.

I have changed the External Content settings in the Trust Center to "Enable all data connections (not recommended)" and "Enable automatic update for all workbook links (not recommended)" but I still get the messages.

My Message Bar is set to "Show the message bar in all applications when content is blocked" because I read on Microsoft KB that the other option of "never show information about blocked content" will still block the content, just not tell you about it, which doesn't fix my issue.

The files I'm opening and the files linked to and from them are all on the same shared drive on a file server in the office. Is there a way to tell Office '07 that our file drive is a trusted source by default?

## Excel 2007 :: Real Time Formula Change When Cell Selected

Mar 6, 2012

I am an Excel 2007 user.

I have a formula (below) that looks only at records based on specific filtering criteria or conditions; one of those conditions is the month of the year: ('Data Dump'!\$Q:\$Q,Summary!\$A\$26). This portion of the formula would change as a month is selected.

Opposed to having to rewrite the formula every time that I want to view a specific month, I want to automate the formula to change the first condition when a user selects a month from Column A. This would be a real time, automatic refiltering and recalculation as the user selects different months from Column A

Current Formula that filters and shows only January records.
=COUNTIFS('Data Dump'!\$Q:\$Q,Summary!\$A\$26,'Data Dump'!\$D:\$D,Summary!\$A\$2,'Data Dump'!\$M:\$M,Summary!C\$2)

Column A (January is in cell A26)
January
February
March
April
May
June
July
August
September
October
November
December

## Vba Error Run-time 429 Activex Component Can't Create Object - Excel &amp; Outlook 2007

Aug 11, 2008

I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error:

Run-time error '429':
ActiveX component can't create object

and is stopping on the line

Set OutApp = GetObject(, "Outlook.Application")

FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error.

Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String

'Set OutApp = CreateObject("Outlook.Application")
Set OutApp = GetObject(, "Outlook.Application")
If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")

OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

## Excel 2007 :: Time Difference Calculations And Automatic Insertion Of Text In Blank Column

Jan 4, 2014

Please find attached a daily copy of a spreadsheet that is used to monitor train running times.

The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.

The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.

Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.

For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)

What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.

To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.

My problem is two fold.

Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.

Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.

DAILY TRAIN RUNNING.xlsx‎

## Excel 2007 :: Change Source Workbook For All External References At Same Time But Avoid Browsing?

Jan 7, 2013

Need to create Working order (in Excel 2007) for filling machine in my factory. Working order is changeable from day to day and the deference is product quantity. Product quantity in working order [File - Working order] is changing according to the each sales plan [7-Jan-13, 8-Jan-13] - (File Working order is Linked to the appropriate sales plan in this case - [7-Jan-13]).

Anyway, I need to change the source workbook "sales plan" from [7-Jan-13] to [8-Jan-13] by changing source workbook name in red cell [File - Working order] and not to browsing location?

All source workbooks [sales plan 7-Jan-13, 8-Jan-13]are at the same location in the same folder, only difference is the source workbook names. So, is it possible to change source workbook name (File - Working order - red cell) for all external references (File - Working order - green cells) at the same time, and avoid annoying browsing?

## Deleting All VBA Upon Closure

May 4, 2009

This thread is somewhat of a cross post, the cross post is here
[url]
The cross post relates to a MS-Word related question.

However, I do have the same question, just for MS-Excel.

Right now my Macro runs when my Excel application opens.

But when my users are Xing(closing) out of the Excel application is there a way to remove all VBA upon closure of the Excel sheet.

## Call A Macro Upon Datasform Closure

Mar 3, 2009

I am tyring to make my database as foolproof as possible and one thing I need to be able to do is run a macro when the excel dataform is closed. I am guessing this is a worksheet event, so this is what I put together but it does not sort.

## Automatically Formatting To Date Upon Closure

Jan 7, 2014

I have found that periodically Excel is automatically reformatting everything in large workbooks to Date Format. Initially I save these workbooks with General, Number, Currency, Text, and Date formatting within the cells. Upon opening at a later time, everything is converted to Date Format.

## Creating Sale Closure Charts Using Date Ranges?

Jun 29, 2014

I want to find the number of similar values contained in a column based on a date range I specify.

I have a data set that's formatted like the following (you can see the full data set at [URL] but I've hidden the irrelevant columns and highlight the relevant ones)

[Code] .......

I want to use this data to create two types of graphs. One showing the ratio including all values in the Status column. There are 4 total (Open, Lost, Won, Won-Job Cancelled). I want another which excludes the "open" status from the ratio figure. I've create some images demonstrating what I want to accomplish at [URL] .....

Additionally, I want to be able to specificy date ranges for these charts. Specifically 0-30 days from todays date, 30-90 days and older than 90 days.

I want to accomplish this using Google Sheets (the latest version) and know how to use the graph total. It's just creating the data the way I need it which is proving to be the most challenging.

## UDF Not Updating As Expected

Jul 31, 2007

I have a written the function below, but when ever I use it, and for example drag it across lots of cells, they all come up with the same value, and I have to manual click on each one and pres enter to get it to show the right value. I have tried searching but without much luck as I am not sure what I should be searching for. Using application.volatile doesn't.

Option Explicit
Public Function FirstLinePickUp(inputrow As Variant) As Variant
Dim n As Integer
Dim testcell As Variant
n = 0
testcell = ""
Do Until testcell <> "" Or ActiveCell.Column - n <= 0
testcell = Cells(inputrow.row, ActiveCell.Column - n)
n = n + 1
Loop

FirstLinePickUp = testcell

n = 0
testcell = ""
End Function

## Excel 2010 :: Insert Date And Time In Column Upon Data Change For First Time Only

May 3, 2013

I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.

Note: If the column I already have the date and time inserted before then it should give message record already have date and time.

I am using office 2010.

## Excel 2011 :: Making Time Sheet - Converting Numbers To Time

Jul 11, 2014

A client buys 500 minutes of my time. In one week I spend 340 minutes on the account. I'd like a column to show Time purchased (say 500 Minutes) Time spent and a final section showing time left (but showing negative values in red)

I hope I've explained this ok but here is an illustration of what I'm trying to achieve in Excel 2011.

[URL] ...........

## Excel 2013 :: Separate Time From Date To Sort Time Frames Over Multiple Days?

Jun 17, 2014

I have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.

Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?

I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013

## Converting Normal Time To Epoch Time Format Using Excel

May 18, 2003

I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format.

Is there a function or vba code that I can use in excel to convert the normal time format to epoch time?

I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.

## Run-time Error 9 When Upgrading To 2007

Feb 18, 2010

Our office has recently upgraded from Excel 2003 to Excel 2007, and now I am having an error message with my workbook. The error is as follows:

Run-time error '9':
Subscript out of range

I believe I have all the macro security settings set up, however, I think this is a problem with a printing macro I had help with here. It was created to print the number of pages entered into a specified cell when the Print button was clicked - I think 2007 uses a slightly different button called 'Quick Print'? I'm just guessing here - but when I hit the Quick Print button, the Print Options box appears instead of immediately printing.

## Ending Macros (End With And/or End Sub Is Expected)

Nov 12, 2008

I have a macro that continues to tell me that an End With and/or End Sub is expected. I continue to add these statements in various ways, but the macro will not complete properly.

Here is the