Re: Convert Number To Hours & Minutes
Formatted as a number to two decimal places.
Re: Convert Number To Hours & Minutes
Formatted as a number to two decimal places.
How can I convert an ordinary number i.e. 3.45 to Hours & Minutes?
A colleague has columns of data that when summed are all wrong i.e. 8.65 instead of 9 Hours 5 minutes.
I have a macro that sequentially opens a series of files goes to B4 and if the value in that cell = "Closed" then it runs a sub routine that inserts some calculations and a link to another workbook.
The word "Closed" could appear at any time.
So if I run the macro this month and there is a file called File1 and B4 = "Closed" then it will run the sub routine and ceate a link.
if I run the macro next month because File1 will still have closed in it it will run the sub routine again.
Is there a line of code I could put in right at the beginning which searches for a link to the other file and if it finds it doesn't run the sub routine in question?
The filepath to the linked file is -
'H:\HOME\timc\EXCEL\BAD_DEBT_REPORTS\FY2007\BAD_DEBT_2007.XLS
Thanks
I am using this macro to ensure that a range of cells appear in Proper Case.
However I am encountering a drawback, sometimes I have text which I want in Upper Case but which is changed into Proper Case. I was wondering if there was a way to work around this.
Example: Practical W/W appears as W/w or Woodturing (GMC) appears as Woodtrunign(gmc)
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A15:A40
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A15:A40")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub
Display More
Re: Combine Cells Containing Partial Dates
Hi Ranger that's fixed the problem.
Many thanks.
I am working on a workbook which has the date in three columns.
Day In Column G
Month In Column H
Year In Column I
Data starts in G3
The data was originally saved as a text download and emailed from another company.
I have tried all sorts of formatting but nothing seems to work.
I need to somehow get the dates i.e. 26 10 00 or 26th October 2000 to appear as Oct 00.
Does anyone have any ideas.
The current format of the three columns is set to dd for colum G mm for column H and yy for column I.
But because the date is split nothing seems to work.
Have to go to the Dentist now will look for any advice tomorrow morning.
Many thanks
Re: Date Based on Other Dates
Thanks Kris & everyone else who contributed.
Re: Date Based on Other Dates
Kris, BTC2 and everyone else that has helped on this problem.
Many thanks it is sometimes difficult to explain exactly what you are trying to do and I think I have probably not explained this problem very very well.
In my defence I can only say that I have this awful flu bug that is going around the UK at the moment and I feel pretty lousy.
Many thanks for the solutions you have provided and apologies for any shortcomings on my behalf when trying to explain what I wanted to achieve.
Tim.
Re: Date Based on Other Dates
Hi Kris,
See Attached.
Tim.
Re: Date Based on Other Dates
Hi Kris,
Let me re-phrase my question to someone with infinitely more experience than me.
I have ammended the formula as per the post before last and it seems to work.
In your opinion is it a viable solution? i.e. Have I unwittingly created an unstable formula that works in testing but may give me a wrong result.
I was just asking if someone could look at the formula to see if it was viable, sorry to have caused offence.
Re: Date Based on Other Dates
Bumped.
Do you think my solution will work for February?
Re: Date Based on Other Dates
How about this for a solution?
In B9 =A14-A13
Take Feb From Mar giving 28
In B10 =A26-A25 (Feb Next Year)
or 29 next year
Ammend calculation to this just for the two February cells. I have highlighted in bold the extra If Statement.
=IF(B$10>$A$9,DATE(YEAR($A13),MONTH($A13),A$9),IF(B$8=0,DATE(YEAR($A13),MONTH($A13),B$10),IF($B$8=5,DATE(YEAR(A13),MONTH(A13)+1,1)-WEEKDAY(DATE(YEAR(A13),MONTH(A13)+1,DAY)),DATE(YEAR(A13),MONTH(A13),1+($B$8*7))-WEEKDAY(DATE(YEAR(A13),MONTH(A13),DAY)))))
And this for next Feb
=IF(B$10>$A$9,DATE(YEAR($A13),MONTH($A13),A$10),IF(B$8=0,DATE(YEAR($A13),MONTH($A13),B$10),IF($B$8=5,DATE(YEAR(A13),MONTH(A13)+1,1)-WEEKDAY(DATE(YEAR(A13),MONTH(A13)+1,DAY)),DATE(YEAR(A13),MONTH(A13),1+($B$8*7))-WEEKDAY(DATE(YEAR(A13),MONTH(A13),DAY)))))
It seems to work okay.
Re: Date Based on Other Dates
Hi Kris,
Your example won';t work because as I said in an earlier post I either us B* & B9 or B10 not all of them.
The cells in B8, B9 & B10 are links to another workbook.
In that workbook I have the Week Number, Day & Pub Date.
If the publication comes out on the fourth Thursday of every month the first two columns are utilised. If the publication comes out on a specific date the third column is utilised and the first two are left blank.
Re: Move All Used Cells on Each Row
Thanks Kris.
Re: Date Based on Other Dates
If I paste into B11 and copy down I get Fri 02/03/07 in B13 if I put 30 in B10.
Re: Date Based on Other Dates
If I paste that formula straight into B13 I get Sat 30/12/06 instead if 28/02/07.
Re: Date Based on Other Dates
It's an either or situation.
I will either have say 1 in B8 and Monday in B9 to give me the date of the first Monday in each month all the way down the column.
or B8 & B9 will be blank if so then -
If I put 30th in B10 I should get the 30th of each month all down the column.
But because Feb has 28 or 29 days it is showing a 2nd or 3rd March.
I won't be using all three cells at the same time.
Re: Date Based on Other Dates
Hi Kris, sorry I don't understand. B8 is reserved for 1st to 5th and B9 is reserved for Sunday to Saturday, B10 is for 1st to 31st.
In the UK there is a publication called BRAD British Rates & Data, and every publication in the Country is listed in it.
Under each entry is all of the technical data regarding each magazine including the publication date. These fall into two categories the 1st,2nd,3rd & 4th individual named day of the month with 5th being used for five week months.
The other category is by date i.e. always published on the 30th. Obviosuly this is impossible for February and they would publish on the 28th or 29th so February needs some sort of override so that if B10 > 28 or 29 depending on the year it only ever puts in 28 or 29.
Re: Move All Used Cells on Each Row
I need to resurrect this post because another factor seems to be tripping the macro up.
My original query was how to move data that is dotted all over the place between rows H and IV to a back to a common start in H.
I.E. a row of data starting in K5 and running across to AB5 is cut and pasted into H5.
This works fine the first time that you run the macro it but if you add data to the bottom of the worksheet and run the macro again it seems to change all of the data in column H which has already been moved.
Any ideas anyone?
Sub MoveData()
Dim LastRow As Double
LastRow = ActiveSheet.UsedRange.Rows.Count + 3
Application.ScreenUpdating = False
For i = 4 To LastRow
If WorksheetFunction.CountA(Rows(i)) > 0 Then
If Range("IV" & i).Value = "" Then
Range(Range("H" & i).End(xlToRight), Range("IV" & i).End(xlToLeft)).Cut Destination:=Range("H" & i)
Else
Range(Range("H" & i).End(xlToRight), Range("IV" & i)).Cut Destination:=Range("H" & i)
End If
End If
Next i
Application.ScreenUpdating = True
End Sub
Display More
Re: Date Based on Other Dates
Hi Kris, see attached.