Use this:
=IF(A3<=3,"Q1",IF(A3<=6,"Q2",IF(A3<=9,"Q3","Q4")))
Use this:
=IF(A3<=3,"Q1",IF(A3<=6,"Q2",IF(A3<=9,"Q3","Q4")))
QuoteUnfortunately, 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,
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.
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
Thank you.
This calculates the time for one cell- C6.
CodeDisplay MoreSub 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
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)
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.
Name | Tech Grade 1 | Tech Grade 2 | Tech Grade 3 |
Joe | y | y | y |
Dave | y | y | |
Karen | y | y | |
Bob | y | y |
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.