Search:

Type: Posts; User: 63falcondude; Keyword(s):

1. Thread: Difference between two dates in hours

by 63falcondude
Replies
4
Views
161 Re: Difference between two dates in hours

Try this in D2:

=(DATE(2018,MID(C2,4,2),LEFT(C2,2))+RIGHT(C2,5)-(DATE(2018,MID(B2,4,2),LEFT(B2,2))+RIGHT(B2,5)))*24

or
...
2. Thread: Reference Lookup need for a Name and Date set

by 63falcondude
Replies
3
Views
122 Re: Reference Lookup need for a Name and Date set

Great! Happy to help.
3. Thread: Reference Lookup need for a Name and Date set

by 63falcondude
Replies
3
Views
122 Re: Reference Lookup need for a Name and Date set

With your first range in A1:E10 and your second range in G1:J7 (headers in row 1), try this in D2:

=LOOKUP(2,1/((G\$2:G\$7=B2)*(H\$2:H\$7<=A2)*((I\$2:I\$7>=A2)+(I\$2:I\$7=""))),J\$2:J\$7)
4. Thread: Please I need a quick help

by 63falcondude
Replies
6
Views
271 Re: Please I need a quick help

You're welcome. Happy to help.
5. Thread: Please I need a quick help

by 63falcondude
Replies
6
Views
271 Re: Please I need a quick help

Try this in B16:

=SUMPRODUCT((B\$2:E\$2=A16)*(B\$10:B\$14="x")*(B\$3:E\$7="x"))
6. Thread: SUMIF Issue?

by 63falcondude
Replies
4
Views
248 Re: SUMIF Issue?

Cross-posted here:

https://www.excelforum.com/excel-formulas-and-functions/1212433-sumif-issue.html
7. Thread: how do i format date with text in a formula?

by 63falcondude
Replies
3
Views
102 Re: how do i format date with text in a formula?

Try

TEXT(OFFSET((PIF_REPORT!\$J\$4,1,0,1,1),"mm-dd-yyyy")&" is the date i'm looking for"
8. Thread: formula to find the lowest number?

by 63falcondude
Replies
16
Views
410 Re: formula to find the lowest number?

Just FYI: If you wanted to return the header of the smallest value, you can use this formula:

G2 =INDEX(B\$1:F\$1,MATCH(MIN(B2:F2),B2:F2,0))

This assumes that your data is in A1:F5
9. Thread: Multiple identical results with VLOOKUP

by 63falcondude
Replies
1
Views
112 Re: Multiple identical results with VLOOKUP

If you are looking to take the sum of something, try using the SUMIF function.
10. Thread: How to sumproduct cell arrays?

by 63falcondude
Replies
10
Views
477 Re: How to sumproduct cell arrays?

Cross-posted here:

https://www.excelforum.com/excel-formulas-and-functions/1206520-how-to-sumproduct-cell-arrays.html
11. Thread: IF Statement

by 63falcondude
Replies
4
Views
142 Re: IF Statement

Maybe something along the lines of this:

=LOOKUP(A1,{"A","B","C","D","E","F"},{1,2,3,4,5,6})

by 63falcondude
Replies
10
Views
280 Re: Sequence

You're welcome. Glad we could help.

by 63falcondude
Replies
10
Views
280 Re: Sequence

Sorry, typo...

Should have been

B3 =IF(C3=1,B2+1,B2)

by 63falcondude
Replies
10
Views
280 Re: Sequence

With 2000 in B2, try this in B3

=IF(C3=1,B2+1,B3)

Copied down.
15. Thread: Converting a text date to numnerical date

by 63falcondude
Replies
3
Views
219 Re: Help needed Converting a text date to numnerical date

You have a couple of options here.

What I would do in this scenario, is highlight the column with the text dates > Data > Text to Columns > Next > Next > Date: DMY > Finish.

Then you can format...
16. Thread: Nestled Sum, If and Year

by 63falcondude
Replies
7
Views
507 Re: Nestled Sum, If and Year

I put the sample data from post #5 into a new worksheet.

Both of the formulas suggested in post #3 gave me 0.006

=SUMPRODUCT((YEAR(1:1)=YEAR(I16))*(6:6))/1000
and...
17. Thread: MIN with 3 IF conditions

by 63falcondude
Replies
5
Views
283 Re: MIN with 3 IF conditions

Personally, I write MIN IF functions like this:

=MIN(IF((condition1)*(condition2)*(condition3),result)) Ctrl Shift Enter

Just swap the comma for a semicolon if your regional settings use ";"...
18. Thread: Nestled Sum, If and Year

by 63falcondude
Replies
7
Views
507 Re: Nestled Sum, If and Year

Try

=SUM(IF(YEAR('Skylights P&L'!1:1)=YEAR(I16),'Skylights P&L'!6:6))/1000 Ctrl Shift Enter

or

=SUMPRODUCT((YEAR('Skylights P&L'!1:1)=YEAR(I16))*('Skylights P&L'!6:6))/1000

Note that you...
19. Thread: Conditional Formatting

by 63falcondude
Replies
5
Views
253 Re: Conditional Formatting

You're welcome. Happy to help.
20. Thread: Conditional Formatting

by 63falcondude
Replies
5
Views
253 Re: Conditional Formatting

You're welcome.

Change that second formula (the red formula) to this:

=AND(AND(\$D3="YES",\$E3="YES",\$F3="YES")=FALSE,COUNTBLANK(\$B3:\$F3)<>5)
21. Thread: Conditional Formatting

by 63falcondude
Replies
5
Views
253 Re: Conditional Formatting

If you want to highlight B3:F3 in Green if =AND(D3="YES",E3="YES",F3="YES") returns TRUE and highlight B3:F3 in Red if =AND(D3="YES",E3="YES",F3="YES") returns FALSE, try this:

Highlight B3:F3 >...
22. Thread: Countif similar text values such as 3.1, 3.10, 4.1, 4.10

by 63falcondude
Replies
4
Views
628 Re: Countif similar text values such as 3.1, 3.10, 4.1, 4.10

COUNTIF sees 3.1 and 3.10 as the same.

Edit: Unless you do what AhoyNC showed in post 4. I've never seen that before.
23. Thread: Need help with countif and indirect.

by 63falcondude
Replies
2
Views
236 Re: Need help with countif and indirect.

Hello and welcome to the forum. I'm glad that you figured out your question and thank you for sharing the solution.

Note that it is important to mention that you are using Google Sheets.
...
24. Thread: Formula Help ?

by 63falcondude
Replies
13
Views
748 Re: Formula Help ?

I'm not saying that this is the answer to your question, but

=SUM(D6:D10+F6:F10)/(D14+F14)

should be

=SUM(D6:D10,F6:F10)/(D14+F14)
25. Thread: IF formula multiple criteria

by 63falcondude
Replies
4
Views
240 Re: IF formula multiple criteria -- Please Help!

You're welcome, glad to help.
Results 1 to 25 of 150
Page 1 of 6 1