Date/time formula question.

[Scootter_DK]

Hi all,

Does anyone have a formula (or formulae) for working out the following:

Don't hesitate to ask me more details, or contact me on MSN...

I have a worksheet with one column showing date and time, in HH:MM (North American Format) and a second column showing temperature readings taken every 15 minutes.

What I am trying to calculate is the total amount of time (in HH:MM) that the temperature was equal to or below a certain threshold.

I have seen some formulaes for calculating elapsed time, but nothing which can account for this variable.

Any help is greatly appreciated and you are guaranteed a free beer should you ever be in Copenhagen!

[VBA Noob]

See if you can get this to work

Please Login or Register  to view this content.

VBA Noob

[Scootter_DK]

See if you can get this to work

Please Login or Register  to view this content.

VBA Noob

Hmmm, I am not sure that would do the trick.. I assumed I would need some sort of IF statement, i.e. IF B1:B1200 =<2,0 etc...

But I am happy to be corrected.

Also, on a side note, any idea why my version of Excel (2000 SP3) needs semi-colons as opposed to commas?...

[VBA Noob]

Post a sample of your file.

You can set semi colon etc under Tools > options > international tab

VBA Noob

[Scootter_DK]

Thanks for that VBA Noob, but it returned a result of 0...

Here is an example of the data as it is laid out (although I think I could modify this if needed)

Date & Time Temp Reading

2007-8-31 1:09 PM 3,2
2007-8-31 1:11 PM
2007-8-31 1:13 PM
2007-8-31 1:15 PM
2007-8-31 1:24 PM 3,3
2007-8-31 1:26 PM
2007-8-31 1:28 PM
2007-8-31 1:30 PM
2007-8-31 1:41 PM


What I would like is a value somewhere which shows any deviations or exceptions from a specific value, in this case, 2,0. Once I can work out the calc, I can then make it whizzy on another sheet to flag deviations, or graph it, etc...

[VBA Noob]

Can you show the expected results as well so I'm clear

PS

calculate is the total amount of time

This is a totally different question to your first

VBA Noob

[Scootter_DK]

AHA!

Occam's Razor is right again...

As I know the temperature is taken every 15 minutes, and I have a reading, if I simply use something like:

=CountIF(A1:A12,<=2.0)

This would return the number of times it dropped below the threshold, and as I know readings are taken each 15 minutes, could I just not multiply it by 15, resulting in the total number of minutes... but then I need to know how to convert this numeric value into HH:MM

[Scootter_DK]

Can you show the expected results as well so I'm clear

PS



This is a totally different question to your first

VBA Noob

Sorry.. I was struggling to work out how to express it as a question... (just moved here and sleep deprived still)...

[VBA Noob]

Maybe

=COUNTIF($B$1:$B$12,">=2")*15/1440

format as time

VBA Noob

[Scootter_DK]

Maybe



format as time

VBA Noob

Hmm close.. but it returns a result of 1:01 for a 60 minute period... may I ask why we divide by 1440? I have seen it in other time/date formulae, and was curiouse... btw, thanks for the help apologies if these are simple questions..

[VBA Noob]

See link re time

http://www.ozgrid.com/Excel/date-time-calculations.htm

On my attachment I got 30 mins. 2 x 15

VBA Noob

[Scootter_DK]

Hmmm, when I opened your example I got, 12:30:00 AM

I think I may have worked it out using:

=CONCATENATE(ROUND(ROUND(((B1231)*1440);0)/1440/60;0);" Hours
";TRUNC(MOD(ROUND((B1231)*1440;0);1440)/60);" Minutes
")


Where B1231 is the formula: =COUNTIF(B3:B1227;"<2")*15


Just checking my results manually now...

[Scootter_DK]

Hmmm, something seems askew, I think it's a simple fix as if I increase the value and expect the time to move by 15 minutes, it moves by one hour... must be something in the calc...

[VBA Noob]

You will do because of the , and ;

You need to change in options as I mentioned

VBA Noob

[Scootter_DK]

You will do because of the , and ;

You need to change in options as I mentioned

VBA Noob

Are you sure? I would not have thought that would make a difference as Excel is just seeing ; as a , so it should be ok...

[VBA Noob]

This is what it looks like to me.

VBA Noob

[Scootter_DK]

You will do because of the , and ;

You need to change in options as I mentioned

VBA Noob

Are you sure? I would not have thought that would make a difference as Excel is just seeing ; as a , so it should be ok...

[Scootter_DK]

Aha, interesting, I have no International settings option...

At any rate here is the formulae I am using...

Formula 1

=COUNTIF(B3:B1227;"<2")*15

This gives me the amount of minutes the deviation occured


Formula 2

=CONCATENATE(ROUND(ROUND(((B1231)*1440);0)/1440/60;0);" Hours
";TRUNC(MOD(ROUND((B1231)*1440;0);1440)/60;0);" Minutes
")

This is supposed to give me the number of minutes neatly displayed in Hours and Minutes, however; what is happening is that if another deivation occurs, the hours progress but not the minutes...

Can anyone think of the problem, or perhaps recommend another formula to display xxxxx minutes as xxh xx min?

BTW, don't worry about the semi-colons, they function as commas do in the North American version... and I would prefer not to change this as the end users won't have the wherewithal to change their settings...

[Scootter_DK]

I will try a different question...

Is there a simple way to take a cell which is showing 46.25 to show this as 46 hours and 15 minutes?

Or 46.5 as 46 hours and 30 minutes, etc?

Thanks!

[Scootter_DK]

Thanks for moving this into the same thread.. I suppose I was being a little cheeky starting a new one...

At any rate, anyone have an idea?

[daddylonglegs]

If you have 46.25 in A1 then try

=TEXT(A1/24;"[h] \hour\s m \mi\nut\e\s")

If you want to work directly with the number of minutes, i.e. A1 contains 2775 change the formula to

=TEXT(A1/1440;"[h] \hour\s m \mi\nut\e\s")

[NBVC]

also:

=INT(A1) & " hours and " & MOD(A1,1)*60 & " minutes"

[Scootter_DK]

Thanks for that, but no luck, I got a VALUE error...

I did work out the formula to show it as hh:mm, which is:

=SUM(COUNTIF(B3:B1227;"<2")*15)/(24*60)

With the cell formatted to Custom, [t]:mm

But my preference would be to show xx Hours xx minutes

[daddylonglegs]

also:

=INT(A1) & " hours and " & MOD(A1,1)*60 & " minutes"

That's also a good way but you might need to include some rounding element because 46.6 in A1 would give a result like

46 hours 36.0000000000001 minutes

so I'd change to

=INT(A1) & " hours and " & ROUND(MOD(A1,1)*60,0) & " minutes"

[NBVC]

That's also a good way but you might need to include some rounding element because 46.6 in A1 would give a result like

46 hours 36.0000000000001 minutes

so I'd change to

=INT(A1) & " hours and " & ROUND(MOD(A1,1)*60,0) & " minutes"

Thank daddylonglegs...you're right... I only tested the sample given...should've played with it a bit

[daddylonglegs]

Thanks for that, but no luck, I got a VALUE error...

I did work out the formula to show it as hh:mm, which is:

=SUM(COUNTIF(B3:B1227;"<2")*15)/(24*60)

With the cell formatted to Custom, [t]:mm

But my preference would be to show xx Hours xx minutes

Do you mean using TEXT function? I don't know if that'll work correctly with a non-English version of Excel. Try modifying NBVC's suggestion, i.e.

=INT(COUNTIF(B3:B1227;"<2")/4)&" hours "&ROUND(MOD(COUNTIF(B3:B1227;"<2")*15;60);0)&" minutes"

[Scootter_DK]

YAHOOO!

Daddylonglegs, if you are ever in Copenhagen let me know, beer is on me!

[daddylonglegs]

Having looked a bit closer then given that your number of minutes is based on a COUNTIF which can only return whole minutes and therefore the final result is always a multiple of 15 minutes, you don't need ROUND function, so my previous version should work but you can shorten to

=INT(COUNTIF(B3:B1227;"<2")/4)&" hours "&MOD(COUNTIF(B3:B1227;"<2");4)*15&" minutes"

[Scootter_DK]

OK great, thanks again.

As I just started with this company, it's a chance to make a good and helpful impression on my colleagues.

[daddylonglegs]

I did work out the formula to show it as hh:mm, which is:

=SUM(COUNTIF(B3:B1227;"<2")*15)/(24*60)

With the cell formatted to Custom, [t]:mm

But my preference would be to show xx Hours xx minutes

Final thoughts....

Your suggestion here is probably a better approach actually, because the result, while showing as a time, is still a value, so you can use it for calculations. You should be able to use the simpler formula

=COUNTIF(B3:B1227;"<2")/96

and custom format that cell as

[t] "hours" m "minutes"

[I assume "t" is Danish notation for hours?]