# Search:

Type: Posts; User: Gerald Higgins; Keyword(s):

Page 1 of 6 1

1. ## Thread: Time Duration Between Two Dates and Times

by Gerald Higgins
Replies
13
Views
393

### Re: Time Duration Between Two Dates and Times

Yes applying a SUM formula to get a total should work.
2. ## Thread: Calculating difference between opened date/time and closed date/time and excluding weekends, holidays and off business hours

by Gerald Higgins
Replies
9
Views
157

### Re: Calculating difference between opened date/time and closed date/time and excluding weekends, holidays and off business hours

OK, I'm going offline now, but will take a look again tomorrow.
3. ## Thread: Calculating difference between opened date/time and closed date/time and excluding weekends, holidays and off business hours

by Gerald Higgins
Replies
9
Views
157

### Re: Calculating difference between opened date/time and closed date/time and excluding weekends, holidays and off business hours

In post #6 , I think you have stated the "Time to Closure" incorrectly in some cases.

For case 1901-3699490, I assume you treat 2nd Jan as a working day, therefore I think the time to closure is...
4. ## Thread: enter 1 if cell is negative number, zero if positive

by Gerald Higgins
Replies
3
Views
117

### Re: enter 1 if cell is negative number, zero if positive

Or slightly simpler . . .

=--(G1<0)
5. ## Thread: Another Time comparison problem

by Gerald Higgins
Replies
4
Views
139

### Re: Another Time comparison problem

How do we know that Event B began 0:11:15 after Event A ?
It isn't clear that that's the case, in the data that you have provided.

Also, how do we know which events to compare against ?
Is it...
6. ## Thread: Calculating difference between opened date/time and closed date/time and excluding weekends, holidays and off business hours

by Gerald Higgins
Replies
9
Views
157

### Re: Calculating difference between opened date/time and closed date/time and excluding weekends, holidays and off business hours

Same question again . . .
7. ## Thread: Calculating difference between opened date/time and closed date/time and excluding weekends, holidays and off business hours

by Gerald Higgins
Replies
9
Views
157

### Re: Calculating difference between opened date/time and closed date/time and excluding weekends, holidays and off business hours

Please provide the expected results for all 6 of those cases.
Also, is your date format DD/MM/YYYY or MM/DD/YYYY ?
8. ## Thread: Excel table: mysterious change of formulae

by Gerald Higgins
Replies
5
Views
180

### Re: Excel table: mysterious change of formulae

To help me replicate this situation, which row(s) are your headers in ?
Do these formulas also extend ABOVE the cells quoted, eg. to T6, T5 and so on ?
If YES, please can you provide the formulas...
9. ## Thread: Excel table: mysterious change of formulae

by Gerald Higgins
Replies
5
Views
180

### Re: Excel table: mysterious change of formulae

Hmmm . . .

Re-reading the OP, I don't actually see a question.
If there was one, can you point it out to me please ?

Maybe obvious to you but not to me.

I'll have a look to see if I...
10. ## Thread: MS Excel. How to get around pivoting data that has blank placeholder columns?

by Gerald Higgins
Replies
1
Views
78

### Re: MS Excel. How to get around pivoting data that has blank placeholder columns?

I think your options are to delete the blank columns, OR insert dummy column headers.

Too vague.
11. ## Thread: check if a date falls within a specific month/year

by Gerald Higgins
Replies
15
Views
52,943

### Re: check if a date falls within a specific month/year

Glad you got it working, but which one of my two suggestions did you go with ?

I'm sorry but I don't understand what you mean in your follow up question.
12. ## Thread: Employee Attendance Tracking with Points - help with SUMIF Formula

by Gerald Higgins
Replies
1
Views
77

### Re: Employee Attendance Tracking with Points - help with SUMIF Formula

Hi, welcome to the board.
Your request seems far, far too vague to allow anybody to answer it precisely.

What exactly does this mean . . .

13. ## Thread: Excel table: mysterious change of formulae

by Gerald Higgins
Replies
5
Views
180

### Re: Excel table: mysterious change of formulae

Hi, getting the circular reference warning does not mean that the workbook is corrupted, it means that one (or more) of your formulas refers to itself somehow.

This is called a circular reference....
14. ## Thread: Time Duration Between Two Dates and Times

by Gerald Higgins
Replies
13
Views
393

### Re: Time Duration Between Two Dates and Times

The point about a custom format is that you can enter anything you like as the format.
What you input might not work, but that's beside the point.

In the format cells dialog box . . .
Under...
15. ## Thread: check if a date falls within a specific month/year

by Gerald Higgins
Replies
15
Views
52,943

### Re: check if a date falls within a specific month/year

If you are certain that all dates do actually fall within the 4 years, there are various options.

Nested IF is one option, for example . . .
=if(A1< [year 1 end date],"Year 1",if(A1< [year 2 end...
16. ## Thread: IFBLANK Formula Issue

by Gerald Higgins
Replies
4
Views
92

### Re: IFBLANK Formula Issue

It's not clear what you want exactly, I'm guessing perhaps something like this

=if(isblank(j4),if(isblank(k4),"",k4+30),j4+30)

Add the \$ symbols back in if required.
17. ## Thread: Filter rows based on cell colour

by Gerald Higgins
Replies
1
Views
65

### Re: Filter rows based on cell colour

Hi, this question is asked quite often on this board.

A search using the terms Filter, Rows, Colour gives lots of results and several possible solutions, like this...

by Gerald Higgins
Replies
2
Views
102

### Re: Data Validation

Hi, welcome to the board.

Yes, there are ways of doing this.

Here's one way . . .
Data Validation
Settings
Allow
Custom
Formula
19. ## Thread: check if a date falls within a specific month/year

by Gerald Higgins
Replies
15
Views
52,943

### Re: check if a date falls within a specific month/year

To check whether a single date falls between two dates, something like this should work

=and(A1< =B1,A1>=C1)

where
A1 contains the date to be analysed
B1 contains the end date of the year...
20. ## Thread: Simplify If Formula

by Gerald Higgins
Replies
6
Views
159

### Re: Simplify If Formula

My guess is that the reference to a sheet called 'Today-7', and cell \$B\$1 on that sheet, is a typo.
21. ## Thread: Find closed date and next one open

by Gerald Higgins
Replies
3
Views
155

### Re: Find closed date and next one open

Hmm . . . this is a bit vague.

I don't even know what it means to say it is always true.

22. ## Thread: Find closed date and next one open

by Gerald Higgins
Replies
3
Views
155

### Re: Find closed date and next one open

In your sample data, the data on Sheet 2, column b, always matches the date in Sheet 1, column b, for the same VIN.
Is this ALWAYS TRUE in your data, OR, is it POSSIBLE that there may be dates in...

by Gerald Higgins
Replies
4
Views
214

### Re: Request for help with sumif formula

OK, so there are probably lots of ways of doing this.
If I was doing it myself, I think I would use separate helper rows to
a) identify which year the replacements happen, and
b) calculate the...
24. ## Thread: Help me with Sumifs, Index and Match

by Gerald Higgins
Replies
2
Views
84

### Re: Help me with Sumifs, Index and Match

Seems to be connected to this earlier thread
25. ## Thread: How to align data in different colums

by Gerald Higgins
Replies
3
Views
107

### Re: How to align data in different colums

OK, I understand how your data is laid out now.

I don't understand how you WANT it to be laid out.

Do you want something like this . . .
...............C1.............C2..............C3...
Results 1 to 25 of 150
Page 1 of 6 1