# Thread: If formula using time

1. ## If formula using time

I have a formula that says
=IF(E32-E30<=0.15,"-",E32-E30)

What I am trying to get is when E32 minus E30 is less than 15 seconds its a dash, if its more its the amount. However I struggle with the 15 seconds bit. I wanted to have the formula as =IF(E32-E30=<0.15,"-",E32-E30) but excel moves the = and smaller around

Thanks

2. Karat,

The problem is you need to be comparing TimeValues.

If E32 & E30 are valid TimeValues or DateTimeValues then the comparison value has also to be a valid time value.
to get 15 seconds you need to replace the 0.15 in your formula with TimeValue("00:00:15") then the comparison will be made correctly.
TimeValues.JPG
HTH

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

Karat (2013-09-11)

4. Mr RetiredGeek I hope you can help with the rest of my query. As you see from the attached spreadsheet (a different one) I have to calculate the time when the direction is BA or AB. I've tried the correct timestamp with nested IFs but it isn't working. So what I would like is.. when the timestamp is less than 20 seconds I have to use the minus commend, however, when it is say 20 min there is only one movement.
PS English isn't my first language so I hope you understand.. Thanks heaps

5. Hi Karat

First of all, if you want to test "is less than 15 seconds its a dash", then you don't put the = in the formula.
It should be
=IF(E32-E30<TimeValue("00:00:15") ,"-",E32-E30)

Note that Excel works in units of 'days' when doing time calculations.
So if a cell has a value of 0.75
..it will display as .75 if the number format is General
..it will display as 18:00:00 if the number format is Time
1 day has 24*60*60 seconds, so in General format, 15 seconds 'converted' to days is 15/(24*60*60)

Now, how about attaching that file?

zeddy

6. Sorry for the late reply.. Different time zone and grandchildren around and sticky fingers and keyboards don't mix

I have attached a spreadsheet with an explanation

Thanks again

7. Karat,

I'm having a bit of a problem understanding exactly what calculations to make. It would be helpful if you could fill in the expected values for say the first 6 lines of the worksheet (rows 3 to 8) in column F. Then maybe we can understand how to write the formulas.

You reference cars/trucks in your explanation but there is no indication/identification of such in the file.

If I understand your use of counters you are talking about those rubber hoses that are stretched across a road to count vehicles.

8. Yep you are right about the counters...
I have added a second spreadsheet and on second test I have come up with a not very sound solution.
I have sorted the AB and BAs... so I have a better grasp of the time.

It is possible to do it like that, however, its better when we receive the info, we drop it into a spreadsheet and bingo it calculates..

I refer to cars and trucks because on other columns, I have to also calculate if the vehicles are small, medium or large.. but that's OK.

The formula in this is about my limit... sad but true..

9. Karat,

I'm sorry but I must have a mental block as I still can't understand what you are after or how to calculate it. Maybe someone else here can take a fresh look.

10. I think you need additional information to ensure which cars are linked to which counter and which counter they go over. But I am also confused as to the setup. Are the counters going across the entire road or only the northbound (BA) and southbound side (AB)? If they go across the entire street, a southbound car will run across the AB then the BA not the AB twice (though it will be going South over both). Or are you counting the AB pair as front tires then rear tires? But if front and rear tires how can the incremental pairs not be linked (how can the front go over and not the rear).

Or is it more 2 counters and you can tell the direction. So a Southbound car will counter 1 and then Counter 2 and Northbound will hit Counter 2 then counter 1 (Southbound = 1s, 2s, Northbound = 2N then 1N), so you need to differentitate the counter and direction, not just a direction (AB or BA). But if you have a turn between the counters it gets even more confusing. A car can go over counter 1 and turn into the yard (not going over counter2), so car 2 behind car 1 goes over Counter 2. Ar a car can leave the yard and go over counter2, so it looks like a pair from one car, but is entering and leaving a yard.

If you know the distance between the 2 counters (set and not changing) and the speeds (which are in your workbook) you may be able to use the presumption of constant velocity to help in your IF, but again we need more details about the setup and the possibilities.

As it is from the data you provide, I don't see how you can determine with any reliability, if a particular car enters or exits the yard or just drives through.

Steve

11. Thanks for that. I have attached the same spreadsheet but with an extra page with a drawing. and some more info like the size of the car and the speed.
I actually think that when this is not enough I will talk to them and ask for a third counter across the entry of the yard..

Thank you all for helping me on this. Its bugging me no end and I even got up at 5am this morning because I was thinking about it.. how sad is that

Cheers - J

12. I would think it would be helpful to know if (and I think should be available in the data) if a car is passing over counter 1 or counter2... Where is that information stored?

How far apart are the counters?
Can't you pair them up easier with the weight and other info rather than trying to deduce it?

A formula will need to know if it is counter 1 or counter2 and which direction it is traveling. It will need to know if there is a "pair" (no yard) or an unmatched pass which means an entry into the yard or exit from the yard (depending on the counter and direction). You are not providing that information that I can see...

Steve

13. Well if there is info that tells me when a car goes over the first and then the second counter I don't know..
And if it is obvious I will feel extremely silly for a long time.
For your information I have attached a whole raw file for one day (text file)..
Thanks

14. If it is NOT there, I don't see how you can get the information you are after....

Given 2 the first 2 BAs in teh file:

DS Axle Num Ht YYYY-MM-DD hh:mm:ss Dr Speed Wb Hdwy Gap Ax Gp Rho Cl Nm Vehicle
00 0000000b 04 2013-08-19 10:52:33 BA 39.8 2.8 1376.4 1376.5 2 2 1.00 1 00000010 SV o o
00 00000013 04 2013-08-19 10:52:46 BA 45.5 2.4 12.9 12.7 2 2 1.00 1 00000010 SV o o

There are several options that need to differentiated (there are probably more, but these will suffice as examples):
A car traveling at 39.8 kph is heading west and crossed the first Counter and then speeds up to 45.5 kph to pass the 2nd
A car has left the yard and heads West, crossing the 2nd counter at 39.8 kph and a 2nd car crosses Counter1 at 45.5 kph
A car crosses Counter1 at 39.8 kph, goes into the yard and 2nd car has left the yard and heads West, crossing the 2nd counter at 45.5 kph
A car crosses Counter1 at 39.8 kph heading to Counter 2, but the 2nd car crosses the 1st counter at 45.5 kph before the 1st care reaches the 2nd counter
A car crosses Counter1 at 39.8 kph goes into the yard and 2nd car crossing the 1st counter at 45.5 kph
A car has left the yard and heads West, crossing the 2nd counter at 39.8 kph and a 2nd car leaves the yard and crosses Counter2 at 45.5 kph

I am not not sure how using 15 seconds to test the difference can differentiate all of these possibilities...

Steve

15. ## The Following User Says Thank You to sdckapr For This Useful Post:

Karat (2013-09-15)

16. Thanks so much for looking at this. I think I will suggest to have a 3rd counter at the yard.. At least all the hours of figuring it out and not having a sound solution proofs that in fact it cannot be done.
I will tell my manager the "news" this morning and if the councils wants a report they will have to manually do it themselves.

Thanks again for looking into this for me.

Next time you are in New Zealand I will buy you a beer

17. Hi Karat

From the data file, you can extract the data to two sheets depending on direction AB or BA.
You can then use a simple excel formula to identify certain vehicles that definitely went into or came out of the yard.
You could extract these from the lists.
For the remaining records, we would need to know the separation distance between the counters.
However, as Steve has pointed out, there are some situations where it would not be possible to accurately infer whether a vehicle went into or came out from the yard.
For example, a stream of 2-axle SV vehicles could all follow each other, turning left out of the yard etc.
We could detect this if we knew which time value was associated with which particular counter.
For example, if a stream of vehicles all turned right into the yard, only the 'second counter' would be tripped etc.

However, life would certainly be easier for you if you just had a detector across the entrance to the yard!

zeddy

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•