Posts by Ingo_Ingo

Use this:

=IF(A3<=3,"Q1",IF(A3<=6,"Q2",IF(A3<=9,"Q3","Q4")))

Then in sheet A, cell C10 then drag till C13:

=VLOOKUP("*"&$A10&"*",B!$A:$G,6,0)

and

in D10 then drag till D13:

=VLOOKUP("*"&$A10&"*",B!$A:$G,7,0)

Quote

Unfortunately, I need to have 2 values populated in Cell C10 and C11 each, which are reffered to By A10 and A11 on sheet B

So, if it works, it needs to look like this:

5,6	120	Receiption, Cleaner
7,8	125	Gardener, Maintenance Manager

in C10 sheet B:

=VLOOKUP(--LEFT(A10),A!$A$6:$D$13,2,0)&", "&VLOOKUP(--RIGHT(A10),A!$A$6:$D$13,2,0)

and drag to C11

Hi,

In sheet B in cell A10 you have 5,6 and in cell A11 you have 7,8.

Put 5 an 6 in separate cell

Same for 7 and 8

Hi,

1. Maybe we can make your formula smaller. Show your formula.

2. Try to use Name_Range.

3. Cut your formula in small pieces. Depend of your version of Excel you can reduce formula.

Hi,

Yes, I already deleted them.

Thank you.

Thank you RoyUK.

VBA code working perfect.

I get this file from a program. I have no acces to this program.

This is the file. With some data.

Working time.xlsx

No.

Is like in post 18

Say in A2:A54 are data like this:

06:30 cell A2

15:25

---------

21:45 cell A3

06:30

--------

22:30 cell A4

05:45

-------

------=

13:35 cell A54

23:20

and result will be in cells (B2:B54), like 08:15, 12:10, 07:45....

I need also to calculate working hour like this: start time 22:30 and end time - say 05:45

Thank you.

Thanks for reply RoyUK.

My data is in column A from A2:A54 and need answer in column B

Hi,

I need little help with a VBA code to calculate working hour.

Data is like here:

Start hour and end hour are in same cell (Alt+Enter)

Say in A2:A54 are data like this:

06:30 cell A2

15:25

---------

21:45 cell A3

06:30

--------

22:30 cell A4

05:45

-------

------=

13:35 cell A54

23:20

and result will be in cells (B2:B54) something like: 8:20, 11:15, 9:35 etc

Similar with this post but need to calculate hour passing hour 00:00

Collecting data from multiple rows in one cell

Thank you.

Quote from royUK

This calculates the time for one cell- C6.

Code

Sub ProduceTimesheets()

Dim TempName As String
Dim StartTime As Date, EndTime As Date



StartTime = Left(Trim(Cells(6, 3).Value), 5)
EndTime = Right(Trim(Cells(6, 3).Value), 6)

MsgBox Int(DateDiff("n", (TimeValue(StartTime)), TimeValue(EndTime)) / 60) & " hours " & _
 DateDiff("n", (TimeValue(StartTime)), TimeValue(EndTime)) - Int(DateDiff("n", (TimeValue(StartTime)), TimeValue(EndTime)) / 60) * 60 & " minutes"

End Sub

Display More

RoyUK,

Can you modify your VBA code to work with hour after 23:59.

Say in A2:A54 are data like this:

06:30 A2

15:25

---------

21:45 A3

06:30

--------

22:30 A4

05:45

-------

------=

13:35 A54

23:20

and result will be in cells (B2:B54)

Thank you.

If you want use VLOOKUP then use this formula:

=VLOOKUP(SMALL(I8:I17,COUNTIF(I8:I17,"<"&B5)+1),CHOOSE({1\2},I8:I17,H8:H17),2,0)

If in A1 are that time and you want to copy in E1, then

Sub Iancox01()
    [e3] = [a1]
    [e3].WrapText = False
End Sub

PLucas1

,

When I posted formula, you were online ...

Was it so hard to tell if it was OK or not?

A simple thank you would have been enough.

Try this:

Instead Tech Grade 1 in B1, use Custom Formatting "Tech Grade "# then in cell B1 put just number (1 or 2 or ...)

In Sheet1, cell B2, use this formula:

=IF(SUM(ISNUMBER(FIND("TG" & B$1,Sheet2!$B$2:$C$7))*(Sheet2!$A$2:$A$7=$A2))=1,"y","") than drag down till last name

and if you put in C1 number 2, then you can drag formula from B1 to C1 then drag down and so on.

It' NOT the same problem....but

"Please put in your file (without formula) how you want to be."

Hi,

Your formula work for me.

Please put in your file (without formula) how you want to be.

With your formula:

In Conditional formatting you can use this formula:

=SUM(COUNTIF(A2,ProjectCode))=0 where "ProjectCode" is NameRange

Hi,

I'm not a programmer....

Try to format all txt......(who have data)...

In button UPDATE

Sheets("Worksheet").Cells(y, 3).Value = Format(txtReceived, "dd.mmm.YYYY") put data in your format mm/dd/yy or mmm/dd/yyyy ...

Sheets("Worksheet").Cells(y, 4).Value = Format(txtMoved, "dd.mmm.YYYY")

Sheets("Worksheet").Cells(y, 5).Value = Format(txtCompleted, "dd.mmm.yyyy")

Change everywhere you need.