Calculating time taken

Microsoft

Board Regular
Joined
Jun 16, 2016
Messages
108
Hello,

I have a list of times (in order from first to last). I receive this data in a report each night, and it can often contain varying amounts of entries.

I would like to calculate the time between the first entry (in Q2) and the last entry at the bottom of column Q. The issue I'm having is that there can, and will be, blanks in column Q.

Can anyone please help?

Thank you.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Is this to be done in a formula or vba?
Code:
Dim rFirst as range, rLast as range
Set rFirst = Range("Q2")
Set rLast = Range("Q65536").end(xlup)
You could then try rLast.value - rFirst.value
 
Upvote 0
Thanks George.

Probably a daft question - But how do I then get the results of my query to display in cell R1?
 
Upvote 0
Not sure what data you are using for the times, so you may need to watch when moving to the next day.
It would be something like:
Range("R1").value = rLast.value - rFirst.value
 
Upvote 0
In R2

=LOOKUP(2,1/(Q:Q<>""),Q:Q)-Q2
 
Upvote 0
Thanks Mole.

Unfortunately though when I enter that formula I am presented with ########

Any ideas?
 
Upvote 0
your end time is less than your start time or the column is too narrow
 
Upvote 0
I'm an idiot, my mistake.

However, the formula does not take the use the last entry in the list it seems.

As mentioned in my previous post there are blanks in my column.

Any way to get around this other than filling in the blanks with a random character?
 
Upvote 0
accounting for a day roll over

=IF(LOOKUP(2,1/(Q:Q<>""),Q:Q)<Q2,LOOKUP(2,1/(Q:Q<>""),Q:Q)-1+Q2,LOOKUP(2,1/(Q:Q<>""),Q:Q)-Q2)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top