Keep getting error in status bar please help.

[ShaunWhite]

...I've have to get back to you about this a bit later. I'm a bit busy today too. My 85yo mum is getting a new telly today and I have to go round and explain how to use it, I could be gone for quite some time....wish me luck!

I think I can see where you're going with this xls but I obviously want to spend a min or two thinking about how best to do it & I might have a question or two.

[Willygubbins]

...I've have to get back to you about this a bit later. I'm a bit busy today too. My 85yo mum is getting a new telly today and I have to go round and explain how to use it, I could be gone for quite some time....wish me luck!

I think I can see where you're going with this xls but I obviously want to spend a min or two thinking about how best to do it & I might have a question or two.

Cheers Shaun,
Let me know what you need.
Ive got a few more questions as well.

[Willygubbins]

I don't worry about the seconds either nigel, I just prefer to use the same standard for time calcs everywhere so I can share my macros around different sheets. I just like to use the one unit for time, not minutes here, hours there etc.
I'm a creature of habit and this is a 30 year one. To coin a tired old phrase...It's horses for courses.

btw, your code should read ...
=IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)),"-" &VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)))

You had the second part as 5,2 ) so were only deducting the second digit of the minute.

Is there anyway of this showing the total minutes countdown? so it shows 60mins for every hours left in the countdown as well? so for example 02:38:01 = 158 in the cell with the formula applied.

[ShaunWhite]

Hi willy, If you refer to my first posing on this you'll see that I was taking the time in hh:mm:ss format and pulled out the seperate hh, mm & ss bits, and converting them to seconds with something like....

where F4 contains a time in hh:mm:ss format
= (value(mid(f4,1,2)) * 3600) + (value(mid(f4,4,2)) * 60) +value(mid(f4,7,2))

The 1,2 4,2 and 7,2 numbers are the start character and length of the bits you want to extract from the hh:mm:ss format field.
Ie the minutes are 2 characters starting at character 4.

Then divide the result by 60 to give the result in minutes.

[Willygubbins]

Hi willy, If you refer to my first posing on this you'll see that I was taking the time in hh:mm:ss format and pulled out the seperate hh, mm & ss bits, and converting them to seconds with something like....

where F4 contains a time in hh:mm:ss format
= (value(mid(f4,1,2)) * 3600) + (value(mid(f4,4,2)) * 60) +value(mid(f4,7,2))

The 1,2 4,2 and 7,2 numbers are the start character and length of the bits you want to extract from the hh:mm:ss format field.
Ie the minutes are 2 characters starting at character 4.

Then divide the result by 60 to give the result in minutes.

That gives the following results
F4 = 01:27:24
Cell with formula =3694.00
/60 = 61.57

[ShaunWhite]

Break it up into the 3 seperate bits and check the result of each.
F4 might have a leading space so the offsets would need nudging up.

[ShaunWhite]

or maybe try changing all occurences of "F4" to be TEXT(F4,"hh:mm:ss") in case F4 is stored in another format.
I think nigelk suggested that

[nigelk]

Although F4 is formatted as hh:mm:ss, that's only for our benefit.

In E4 I've copied the time shown and formatted it as 'general', and you can see how excel really views the time. If you use the formula = MID(F4,1,2)
it's just returning the 1st two digits of the value as shown in E4, which is 0. Adding the text function (E6) forces excel to return the same number in a format we can understand.

Cheers, NIgel

Sample.xlsx

Ps if anyone wants to see what today is, see E2, formatted as general or, if memory serves correct, 42,789 days since 1st Jan1900!

[Willygubbins]

or maybe try changing all occurences of "F4" to be TEXT(F4,"hh:mm:ss") in case F4 is stored in another format.
I think nigelk suggested that

Although F4 is formatted as hh:mm:ss, that's only for our benefit.

In E4 I've copied the time shown and formatted it as 'general', and you can see how excel really views the time. If you use the formula = MID(F4,1,2)
it's just returning the 1st two digits of the value as shown in E4, which is 0. Adding the text function (E6) forces excel to return the same number in a format we can understand.

Cheers, NIgelSample.xlsx

Ps if anyone wants to see what today is, see E2, formatted as general or, if memory serves correct, 42,789 days since 1st Jan1900!

I Went with this in the end and it seems to work.......

=IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2))+(VALUE(MID(TEXT(F4,"hh:mm:ss"),1,2))*60),"-" &VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2)))

[ShaunWhite]

Willy, I don't know how to add an attachment to a PM. ? Here's what that code I sent you should look like in the VBA editor.

....Can the Excel experts please not rip it apart...it's just rough and ready

Untitled.png

Edit....If you want to copy the worksheet....edit the VBA to put the word 'Private' in front of the word SUB on all the macros. Do it BEFORE you copy the worksheet. That way each page will run independantly, each page will only use it's own 'subs' (ie macros.). If you use the word 'Public' then macros are shared between worksheets.....but that's tmi. Then you'll needed to change evey occurance of "Green Up Sheet 1" to "Green Up Sheet 2" for each sheet's set of macros.

Maybe just use this for now on one sheet, have a play....i'll send you some better code soon for using on multiple sheets using 'ActiveSheet' rather than having to name each one...like i said this is a bit v1.0 because it's late. good luck

eg.
Private Sub Worksheet_Change(ByVal Target As Range)
and
Private Sub StartRecord()

etc

[Willygubbins]

Although F4 is formatted as hh:mm:ss, that's only for our benefit.

In E4 I've copied the time shown and formatted it as 'general', and you can see how excel really views the time. If you use the formula = MID(F4,1,2)
it's just returning the 1st two digits of the value as shown in E4, which is 0. Adding the text function (E6) forces excel to return the same number in a format we can understand.

Cheers, NIgel

Sample.xlsx

Ps if anyone wants to see what today is, see E2, formatted as general or, if memory serves correct, 42,789 days since 1st Jan1900!

Hi willy, If you refer to my first posing on this you'll see that I was taking the time in hh:mm:ss format and pulled out the seperate hh, mm & ss bits, and converting them to seconds with something like....

where F4 contains a time in hh:mm:ss format
= (value(mid(f4,1,2)) * 3600) + (value(mid(f4,4,2)) * 60) +value(mid(f4,7,2))

The 1,2 4,2 and 7,2 numbers are the start character and length of the bits you want to extract from the hh:mm:ss format field.
Ie the minutes are 2 characters starting at character 4.

Then divide the result by 60 to give the result in minutes.

Ok so in an atempt to convert this to total seconds in order to be accurate i have tried the following but have come up with a problem............

So when i use this formula:

=IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2))*60+(VALUE(MID(TEXT(F4,"hh:mm:ss"),1,2))*3600),"-" &VALUE(MID(TEXT(F4,"hh:mm:ss"),5,2)))

It show it in seconds, but they only change in 60 sec blocks ok.....

So when I use this formula:

=IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2))*60+(VALUE(MID(TEXT(F4,"hh:mm:ss"),1,2))*3600+(VALUE(MID(TEXT(F4,"hh:mm:ss"),7,2)),"-" &VALUE(MID(TEXT(F4,"hh:mm:ss"),5,2)))

It comes up with the formula error box....

So when i use this formula:

=IFERROR(VALUE(MID(TEXT(F4,"hh:mm:ss"),4,2))*60)+(VALUE(MID(TEXT(F4,"hh:mm:ss"),1,2))*3600) + (VALUE(MID(TEXT(F4,"hh:mm:ss"),7,2)),"-" &VALUE(MID(TEXT(F4,"hh:mm:ss"),5,2)))

It comes up "You've entered too few arguments for this function", then when i hit ok it high lights the 60.........

[nigelk]

The original formula was only to extract the last 10-15 mins before the off and ignored the hours & seconds, it's outlived its usefulness in a situation like this.

Hours,mins & seconds shown as total seconds try:

=MOD(F4,1)*24*60*60

[Willygubbins]

The original formula was only to extract the last 10-15 mins before the off and ignored the hours & seconds, it's outlived its usefulness in a situation like this.

Hours,mins & seconds shown as total seconds try:

=MOD(F4,1)*24*60*60

Brilliant thanks

[ShaunWhite]

The original formula was only to extract the last 10-15 mins before the off and ignored the hours & seconds, it's outlived its usefulness in a situation like this.

Hours,mins & seconds shown as total seconds try:

=MOD(F4,1)*24*60*60

All these years I've used cell-INT(cell) .. using MOD(cell,1) is just that extra bit nicer. d'oh

[Russell]

=MOD(F4,1)*24*60*60

Nice. I've not seen that one before. I will have to try it out. Thanks for sharing.