# Search:

Type: Posts; User: Joe4; Keyword(s):

Page 1 of 6 1

1. ## Thread: Date calculating as a number in formula

by Joe4
Replies
8
Views
97

### Re: Date calculating as a number in formula

I haven't analyzed your formula, just the one part that you mentioned that you were having issues with.
Let's break the formula down into parts, and see what each part returns:

=Networks!\$C\$62...
2. ## Thread: Date calculating as a number in formula

by Joe4
Replies
8
Views
97

### Re: Date calculating as a number in formula

Did you see my reply/try my suggestion?
I think it should solve your issue, and I explained what you did wrong.
3. ## Thread: How to add up different amounts into separate totals.

by Joe4
Replies
1
Views
39

### Re: How to add up different amounts into separate totals.

Welcome to the Board!

There are various ways to do this:
1. Use a Pivot Table
2. Use Subtotals
3. Use a SUMIF formula

Here are instructions on how to do a SUMIF formula:...
4. ## Thread: Date calculating as a number in formula

by Joe4
Replies
8
Views
97

### Re: Date calculating as a number in formula

If you are doing what I think you are, I believe you have your parentheses in the wrong spot. You are subtracting 42 from the date, then taking the Week Number of that, when I think what you want to...
5. ## Thread: Counting values in 1 column based on a category in another

by Joe4
Replies
2
Views
49

### Re: Counting values in 1 column based on a category in another

Let's say your example is in A1:F10.

Formula to put in C2:

=IF((A1&B1)<>(A2&B2),COUNTIFS(\$A\$2:\$A\$10,\$A2,\$B\$2:\$B\$10,\$B2),"")
and copy down

Formula to put in E2:
...
6. ## Thread: get all sheet names starting with a specific letter

by Joe4
Replies
1
Views
37

### Re: get all sheet names starting with a specific letter

Just add an IF statement seeing if it starts with the letter D:

Sub WORKSHEETLIST()
Dim ws As Worksheet
Dim Counter As Integer
Counter = 0
For Each ws In...
7. ## Thread: VBA to convert column range of date to long int

by Joe4
Replies
8
Views
120

### Re: VBA to convert column range of date to long int

From what I found here: https://social.technet.microsoft.com/wiki/contents/articles/12814.active-directory-lastlogontimestamp-conversion.aspx, it appears that the formula you posted should work.
...
8. ## Thread: VBA to convert column range of date to long int

by Joe4
Replies
8
Views
120

### Re: VBA to convert column range of date to long int

That statement, in and of itself, is incomplete, and does not give enough information. A simple "data type" conversion will not do what you want.
You can easily convert a date to an Integer on an...
9. ## Thread: Conditional formatting based on condition of one cell and value of another

by Joe4
Replies
1
Views
29

### Re: Conditional formatting based on condition of one cell and value of another

Welcome to the Board!

You cannot base Conditional Formatting off of the formatting applied by other Conditional Formatting, but you can apply the same logic.

Let's say that your date is in cell...
10. ## Thread: VBA to convert column range of date to long int

by Joe4
Replies
8
Views
120

### Re: VBA to convert column range of date to long int

Wow, I would love to know the logic behind that formula, what is actually really supposed to represent! I have never seen that type of conversion done on a date before.

In any event, you could...

by Joe4
Replies
4
Views
153

### Re: Access: Keep Navigation pane closed

I often hide it using File -> Options -> Current Database -> Navigation and uncheck the "Display Navigation Pane" box, though that doesn't lock it down or prevent them from using F11 to see it, or...
12. ## Thread: VBA to convert column range of date to long int

by Joe4
Replies
8
Views
120

### Re: VBA to convert column range of date to long int

Please explain exactly how you get from 10/15/2019 to 132155712000000000.
What is the logic?
13. ## Thread: VBA to convert column range of date to long int

by Joe4
Replies
8
Views
120

### Re: VBA to convert column range of date to long int

Is your date entered as a valid date or text?
What exactly are you converting it to?
I ask because Excel already stores dates as integers, specifically the number of days since 1/0/1900. So all...
14. ## Thread: Conditional formatting highlight

by Joe4
Replies
13
Views
371

### Re: Conditional formatting highlight

You have a few issues, including referencing the wrong column in your COUNTIF formula, not locking down the correct ranges, and using WILDCARDS in your criteria.
15. ## Thread: highlight cell according to date difference

by Joe4
Replies
3
Views
75

### Re: highlight cell according to date difference

Try this slight variation:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Dim cell As Range

Set rng = Intersect(Target, Range("F5:F10000"))

...
16. ## Thread: IF statement based on date

by Joe4
Replies
2
Views
78

### Re: IF statement based on date

Try:

=IF(YEAR(C9)=2019,F9+F10,0)

by Joe4
Replies
6
Views
68

### Re: #Value Error

I don't think it like setting numeric fields equal to "".
What if you try:

=IF(ISBLANK(C8),0,H7+F8-G8)
If that does not work, please tell us the contents of C8, H7, F8 and G8.
18. ## Thread: highlight cell according to date difference

by Joe4
Replies
3
Views
75

### Re: highlight cell according to date difference

So, do you only want it to check that as you enter the value in the cell (and not done the road, for values already entered in)?
If so, you can use VBA to do that.

Righ-click on the sheet tab...
19. ## Thread: Conditional formatting highlight

by Joe4
Replies
13
Views
371

### Re: Conditional formatting highlight

I don't know how much more able I will be able to help without seeing your workbook.
As I mentioned before, I cannot download files from my current location, but I can from my home.
So, if you are...
20. ## Thread: Code needed for Max & Min Values

by Joe4
Replies
36
Views
1,043

### Re: Code needed for Max & Min Values

Perhaps it will speed up some it you disable screen updates and calculations until the end of the code too:

Private Sub Worksheet_Calculate()

Dim cell As Range

' Exit if A1 not...
21. ## Thread: If CountIf - Help Displaying Blank Cell

by Joe4
Replies
3
Views
145

### Re: If CountIf - Help Displaying Blank Cell

You are welcome. Glad it works for you!
:)
22. ## Thread: If CountIf - Help Displaying Blank Cell

by Joe4
Replies
3
Views
145

### Re: If CountIf - Help Displaying Blank Cell

Is this what you are looking for?

=IF(COUNTBLANK('Document Request'!K10:K40)=31,"",IF(COUNTIF('Document Request'!K10:K40,"Outstanding")>0,"Outstanding",IF(COUNTIF('Document...
23. ## Thread: Why the lag before Excel calculates?

by Joe4
Replies
3
Views
104

### Re: Why the lag before Excel calculates?

I came across it some years ago (10+) when supporting someone who had workbooks of Excel files with 20-30 sheets of data, spanning dozens of columns and tens of thousands of rows, mostly with VLOOKUP...
24. ## Thread: VBA to search a column for the first instance of a date within a designated month

by Joe4
Replies
4
Views
49

### Re: VBA to search a column for the first instance of a date within a designated month

Are you just trying to get the SUM of a particular month?
If so, instead of doing something like that, why not use a SUMIF, SUMIFS, or SUMPRODUCT formula to sum all the records where your designated...
25. ## Thread: Sort by cities

by Joe4
Replies
4
Views
103

### Re: Sort by cities

You are welcome.
Glad we were able to help.
Results 1 to 25 of 148
Page 1 of 6 1