Time Passed Since Last Saved

Mar 8, 2007

Private Sub Workbook_Activate()
Dim user As String
If Worksheets("Setup"). Range("D6").Value = "" Then
user$ = InputBox("Hello. Please enter your name to inialize the program", "Enter Name")
Worksheets("Setup").Range("D6").Value = user
MsgBox ("Welcome " & user & ". Press 'OK' to continue on to the Main Menu.")
user$ = Worksheets("Setup").Range("D6").Value
MsgBox ("Welcome back " & user & ". Press 'OK' to continue on to the Main Menu.")
End If
Exit Sub

but i would like to expand the "welcome back" msgbox to also display "the last time you were was was (eg: 2 days/3 hrs/14 min) ago" - which i presume would be calculated from the last save.

View 5 Replies


Automatically Change Cell (With Time) Color After Time Period Has Passed

May 23, 2008

I have a protected worksheet. Users wish to be able to track changes in the input cells. The suggested approach for this is to temporarily disable sheet protection and allow them to change the font color, then protect afterwards. What I would like to do is:

i) check whether they are in an input cell
ii) if so, then prompt the user with the 'Font Color' dialog box
iii) apply the font color selected to the input cell

I'm struggling to find the dialog box I need. I can launch the one to change the interior color, no problem (Application.Dialogs(xlDialogPatterns).Show). But that's no use to me, I just want a color palette that specifically relates to the Font Color

View 4 Replies View Related

Months Have Passed Irregardless The Number Of Days Passed

Mar 5, 2010

I need a formula to calculate how many months has passed irregardless how many days had passed. What I mean by that is if I have a starting date of 1/31/2010 and an end date of 2/1/28/2010, that should count as 1 month passed. I tried using the DATEDIF function, but that function is counting number of days, so if only 28 days had passed, that would not count as 1 month.

View 9 Replies View Related

Highlighting Cells When A Period Of Time Has Passed?

Aug 20, 2014

If C2 = 21/06/14

I need F2 to highlight when 60 days have passed from the date entered in C2.

Would this work if placed in F2 =C2+60<TODAY()

No access to excel at the minute to try it

View 1 Replies View Related

Last Saved By Time & Date In Cell

Jul 18, 2009

I'm looking for a way show the username of the person who last saved the spreadsheet, and the date & time it was saved. I would like to be able to ouput this data in certain cells on the worksheet. I have tried using something like:

View 2 Replies View Related

Send Last Saved Date & Time To Cell

Jun 24, 2008

I have a macro that returns the time the sheet was last saved:

Function LSDate()
LSDate = Application.Caller.Parent.Parent. _
BuiltinDocumentProperties("Last Save Time").Value
End Function

Unfortuantely, the cell doesn't update without recalculating the cell. Is there a way to get the cell to update automatically every time the sheet is saved?

View 2 Replies View Related

External File Properties In Cells. Last Saved Time

Feb 2, 2010

I was looking for a way to get external file froperties into a cell!

for example:

Last modified date.

this is what I allready found/tried.

View 8 Replies View Related

Creating A Log That Logs The Last User That Saved The Workbook Including The Date And Time

Mar 16, 2004

Is there any way of creating a log that logs the last user that saved the workbook including the date and time. I would like the log to be a sheet hidden in the workbook. Not many people will be saving the workbook.

View 9 Replies View Related

Highlight Row If Date In Column Is Passed

Aug 1, 2014

I want to highlight the rows in my worksheet when the dates in column 'N' are in the past. I've seen codes to do this using conditional formatting when searching online and in here but the problem is is that it highlights blank cells as well. Is it possible to correct this?

View 8 Replies View Related

Forecast Calculation - Once Month Has Passed

Jan 31, 2014

I would like to know a way to sum the future months dollars only once the month has passed to consider that amount only in my forecast. For eg. If I have a Vendor A contract from Jan - April for $1000/per month in total for $4000. My Forecast should only be Feb-April = $3000. So my total column should only display $3000. Once Feb has passed , the forecast should only be March-April i.e $2000. How to get rolling month sum of forecast once month has passed.

Attached is a sample spreadsheet with different vendors.

Rolling sum after month has passed.xlsx‎

View 5 Replies View Related

Trying Use Isblank & Vlookup When Date Has Passed

Jan 22, 2009

My original formula is:

=IF(ISBLANK(VLOOKUP($E39,'Players Scores'!$A$4:$AV$700,'Players Scores'!AA$3+6,FALSE)) = FALSE,VLOOKUP($E39,'Players Scores'!$A$4:$AV$700,'Players Scores'!AA$3+6,$E39),"")

I am using the above formula but would like it to get the value when the date has passed (when date has been inputed in another cell)

I have added an attachment, The formula begins at J44 to AY44 but when date has entered in the red (D44:D67)section I would like to retreive values for blue section(Q44 and onwards) and not the yellow section(J44 to P44)

This is so when players make a transfer it doesnt include the weeks before that date!!

View 12 Replies View Related

Inserting Data After A Date Has Passed

Jan 15, 2007

What formula would I use to populate a cell?


e13 = 0 until after the 10th of the month on the 11th it = $100.00
If I13 has an amount in it on or before the 10th e13 remains $0.
Each of the 12 line of this rent roll needs to correspond to its own month.

View 10 Replies View Related

Automaticaly Set To Default After Date Passed

Mar 26, 2008

I have a worksheet that has a drop down box with the options PPP, MONTHLY, TOKEN & DEFAULT, next to this i have a date when a payment is expected, what i want to do is automaticaly set this to default after the date has expired, the problem i have is that there is data validatrion in this box so i cant enter a formula, is there a way around this

View 14 Replies View Related

Count The Number Of Events Passed

Apr 21, 2009

[data] ....

Here I have 9 total requirements to be tested in any event. If I wanted to count the number of events passed it wouldn't be 7. See Blah2 it passed Design and Electrical. Separate Events yes, but only one requirement. I need to have a total could of 1 for that as passed. Not two. I'm comfusing myself because this inherited spreadsheet is over 1,000 requirements.

View 9 Replies View Related

Formula To Get Number Of Passed Days

Feb 15, 2010

I have a formula which calculates number of days remaining. But I need a formula to to know how many days passed after a particular date.

Example :-

Date .Days passed
10-Feb-2010 .6


View 9 Replies View Related

Length Of String Changes When Passed To Function

Sep 28, 2006

The following afterupdate procedure for my txtStart1 text box sends the value to the FormatTimeValue function that is coded in a module.

Private Sub txtStart1_AfterUpdate()
MsgBox Len(Trim(Me.txtStart1.vaue)) '<= outputs 1
Me.txtStart1.Value = FormatTimeValue(Trim(Me.txtStart1.Value))
End Sub

As you can see from the bolded text, the length of the value is 1 when outputted from the forms code.

However, when I pass it to the following function in a module it outputs 2 as the length.

Function FormatTimeValue(vTarget As Integer) As String

Dim TimeStr As String

If IsNumeric(vTarget) Then

MsgBox Len(vTarget) '<= outputs 2

Select Case Len(vTarget)

Case 1 ' e.g., user entered 1 so time should be 01:00
TimeStr = "0" & vTarget & ":00"

View 3 Replies View Related

Variable Not Passed Back Correctly

Nov 11, 2006

All the subs below take place in Module4. I have a variable, MoreDates, defined as Boolean. At first it was just dimmed, now I have it as Public. When it's passed to a sub, it's true; in the called sub it's changed to false (and I see it as false when stepping through) and sub is exited. The next line of the calling routine checks if true/false...but it's true again. I'm not passing by value, so I don't know why this is happening.

Do Until MoreDates = False
Query_BigCharts (MoreDates) '***************
If MoreDates Then
temprow = temprow + 1
Range("A" & temprow).Select
lastdate = DateAdd("m", -1, lastdate) ' backs last-written date up a month
Range("D6").Value = lastdate 'send to eom cell
lastdate = Range("D8").Value ' formula in d8 converts to last weekday of the month.
End If
If lastdate < FromDate Then
MoreDates = False
End If
End Sub.............

View 6 Replies View Related

Match Date Passed To Variable

Dec 7, 2006

In my excel sheet, I have date values stored in cells from D7 ti IV7. in cells C8 to C100, I have a data validation which selects the values from cells D7 to IV7. When user selects a particular date in coumns C8 from the list, using my code, I am trying to search for the value stored in cell C8 within the range D7:IV7 using function Application.Match

While running the code by clicking on "Distribute Budgeted Efforts" command button, I am getting an error "Type Mismatch" and error is coming from code line number 27 which is "lInitial = Application.Match(lInitialdate, Range("D7:IV7"), 0) - 1"

My code is as below

Private Sub cmdEffortDistribute_Click()
Dim lCount As Single
Dim lStartDate As Date
Dim lCounter As Single
Dim lBudget As Single
Dim lInitial As String
Dim lInitialdate As String
Dim lInitialWeek As String

I am also attaching excel sheet which I am using here for reference.

View 5 Replies View Related

IF Query; Find Out How Many People Passed In Green

Dec 22, 2008

I have a query where I am trying to find out how many people passed in green, amber and red from a series of data. I know (from the attached file) how to find out how many greens, ambers and reds there were, but how can I find out how many of the 7 in cell B12 passed in Green?

View 2 Replies View Related

How To Change Cell Colour After A Year Has Passed

Feb 9, 2009

I am trying to devise a service schedule that has the installation date in say cell D4 and I would like this to automatically place a date a year from there in to cell E4, then 4 weeks before date comes round i would like the text cell to turn yellow, then on & after the date to turn red. Is this even possible?

View 12 Replies View Related

Looking For A Formula That Counts The Ones That Passed Or Failed The Audit?

Apr 25, 2013

I am working on a sheet that has over 150 tabs of information. It is a simple audit that allows for Yes, No or N/A to be selected for each audit category. I have an Outcome Summary tab that is pulling totals for all the audit information on each tab. I did a CountA statement to count any cells (from all worksheets) that had anything selected, so I have my count for the amount of files audited, but for the life of me I cannot come up with a formula that counts the ones that passed or failed the audit. Needs a formula that counts for Yes as 1, No as 0, N/A as 1 (these don't count against us) and doesn't return an error for a tab that wasn't selected.

View 3 Replies View Related

Lock Cells Passed Current Date

Apr 22, 2008

I want to make a sheet that will lock cells from rows that have passed the yesterday date. In A column i entred the days and the locked row must have the range from A to U. After i read the post from here Lock / Protect Rows Past Due Date any solution.

View 3 Replies View Related

Clear Formula If Date Has Passed Paste Contents

Jun 21, 2006

Have a macro where is some dates have passed the formulas are cleared but if the date is < than TODAY the value is left. All worked ok until I changed to formula to where the date calulated exclued weekends. Have attached example spreadsheet.
Forgot to add the marco was provided by the hard work of fellow Ozgrid memeber (Ger).

View 8 Replies View Related

List Random Numbers Being Passed To TextBox Controls

Sep 3, 2007

how to display the result i made in macro to active worksheet so that i can keep a record of the result made in my random generator,

Sub timer()
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
Dim x As String, i As Byte
x = Format$(Int(Rnd * 1899) + 1, "0000")
For i = 1 To Len(x)
Me.Controls("TextBox" & i).Value = Mid$(x, i, 1)
Application.Wait Now + TimeValue("00:00:03")

End Sub

Private Sub CommandButton1_Click()
End Sub

Private Sub TextBox1_Change()

End Sub

View 3 Replies View Related

Shortening The Formula I Used For Calculating Number Of Public Holidays That Passed

Sep 25, 2009

I am using this formula to calculate a set of PH(Public Holidays) that have passed from a period of 1/4/2009 to 31/3/2010.

=IF(TODAY()>='Team Schedule'!D5,1.5,0)+IF(TODAY()>='Team Schedule'!D6,1.5,0)+IF(TODAY()>='Team Schedule'!D7,1.5,0)+IF(TODAY()>='Team Schedule'!D8,1.5,0)+IF(TODAY()>='Team Schedule'!D9,1.5,0)+IF(TODAY()>='Team Schedule'!D10,1.5,0)+IF(TODAY()>='Team Schedule'!D11,1.5,0)+IF(TODAY()>='Team Schedule'!D12,1.5,0)+IF(TODAY()>='Team Schedule'!H2,1.5,0)+IF(TODAY()>='Team Schedule'!H3,1.5,0)+IF(TODAY()>='Team Schedule'!H4,1.5,0)-4.5

From what u can see here, its a massive formula.... but I am pretty sure that there is a shorter way to key in the formula

'Team Schedule'!D5-D12 and 'Team Schedule'!H2-H4 are PH. disregard the 4.5 that is in the formula as it is used for other purposes. Every PH that has passed will credit a 1.5 into the cell.

View 2 Replies View Related

Conditional Formatting :: Make Cell Blue After 90 Days Passed

Jan 19, 2010

This might be a simple answer but... if A1 had the date 1-Jan-2010, what formula would I used, within conditional formatting, to make that cell turn blue once 90 has passed? So the cell should turn blue on 1-Apr-2010.

View 10 Replies View Related

Lock Cells After Today's Date Passed (VBA Excel Code)

Apr 9, 2009

I am trying to lock cells after today's date has passed so that no one can make changes to it after today's date has lapsed. This is for protective reasons so that people do not remove their names from reserving something after using it. Now the code should disallow locking after cell input entry when today's date hasn't passed so that changes can still be made by the user. I am trying to determine the code to do this but I have no idea as to how to do it.

Here's a scenario: I reserve something for Aprill 11, 2009. I input my name. Since it's April 9th, 2009, I am still able to make changes up and until April 11, 2009. After this date, the cell is locked and no changes can be made, except for the administrator.

View 14 Replies View Related

Count Number Of Loops Passed Through And Display On Excel Status Bar

Jun 8, 2010

Is there a way to count the times a loop has occurred and be able to present a running total in the status bar of MS Excel?

Sub Master_Phone()
' Select cell A2, *first line of data*.


View 4 Replies View Related

Create Everyday Report From A Database For Workdays Of Current Month Passed

Jan 12, 2013

In column A of a database I've calculated workdays for all year (excluding weekends and public holidays).

Every day, I update the numerical data of the database, with the facts of previous day.

In another sheet I've made some monthly, quarterly etc., reports for that data.

What I want, is to have:

1) a report every day, with the sum of specific data (say that in column D), for a period starting the first workday of current month and ending the previous workday of today.

2) the total workdays days for that period.

View 4 Replies View Related

VBA Code Line That Allows To Disregard Dates That Have Passed In Current Emailing Macro?

Apr 11, 2014

The code as it stands allows me to push a button and it immediately picks up on a meeting category and date of meeting, then sends emails to those that are supposed to attend based on a date that is within 7 days from the current date of pressing that button. HOWEVER; it seems to still send the email when the date has passed...is there a line I can put in this code to make it so that every date that has already passed will be discounted from future emails?

The code is as follows:


View 1 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved