Adding time HH:MM when it addds up to more than 24 hours

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a column that adds time which has been formatted to HH:MM in custom
format. Just noticed that once the result reaches 24 hours then it restarts
at zero. Not exactly what I had in mind! Could anyone suggest a workaround
for this please.

Whilst I'm here... I wanted to do a data validation for this range to ensure
that users enter times as HH:MM but couldn't figure out how to do it Time did
not seem to be the right option as users will be adding discreet amounts of
time rather than entering a specific time. Couldn't figure how to use custom;
tried HH:MM but think I'm barking up the wrong tree.
Any help would be very much appreciated.

TIA
 
You can't really restrict how users enter information, just the result of
their input. For instance let's say you use data validation and select
"time", "less than or equal to", and enter "12:00 PM". A user can enter 4:48
or 0.2 and get the same result (4:48 AM). Data validation won't restrict
either input. In fact this data validation is the same as selecting
"decimal", "less than or equal to", and entering "1".
 
Sorry, but doesn't anyone lurk anymore.

The time over 24 hours must have been mentioned several times in the last
couple of days :)

[hh]:mm or [hh]:mm:ss

Steve
 
Thank you!
--
Smudge


Sloth said:
use a custom format of
[HH]:MM

Smudge said:
Hi,

I have a column that adds time which has been formatted to HH:MM in custom
format. Just noticed that once the result reaches 24 hours then it restarts
at zero. Not exactly what I had in mind! Could anyone suggest a workaround
for this please.

Whilst I'm here... I wanted to do a data validation for this range to ensure
that users enter times as HH:MM but couldn't figure out how to do it Time did
not seem to be the right option as users will be adding discreet amounts of
time rather than entering a specific time. Couldn't figure how to use custom;
tried HH:MM but think I'm barking up the wrong tree.
Any help would be very much appreciated.

TIA
Click to expand...
Click to expand...
 
Thank you!
--
Smudge


Bernard Liengme said:
Use custom format [hh]:mm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Smudge said:
Hi,

I have a column that adds time which has been formatted to HH:MM in custom
format. Just noticed that once the result reaches 24 hours then it
restarts
at zero. Not exactly what I had in mind! Could anyone suggest a workaround
for this please.

Whilst I'm here... I wanted to do a data validation for this range to
ensure
that users enter times as HH:MM but couldn't figure out how to do it Time
did
not seem to be the right option as users will be adding discreet amounts
of
time rather than entering a specific time. Couldn't figure how to use
custom;
tried HH:MM but think I'm barking up the wrong tree.
Any help would be very much appreciated.

TIA
Click to expand...
Click to expand...
 
Sorry! Usually a persistent lurker but short of time this week!
--
Smudge


SteveW said:
Sorry, but doesn't anyone lurk anymore.

The time over 24 hours must have been mentioned several times in the last
couple of days :)

[hh]:mm or [hh]:mm:ss

Steve

Hi,

I have a column that adds time which has been formatted to HH:MM in
custom
format. Just noticed that once the result reaches 24 hours then it
restarts
at zero. Not exactly what I had in mind! Could anyone suggest a
workaround
for this please.

Whilst I'm here... I wanted to do a data validation for this range to
ensure
that users enter times as HH:MM but couldn't figure out how to do it
Time did
not seem to be the right option as users will be adding discreet amounts
of
time rather than entering a specific time. Couldn't figure how to use
custom;
tried HH:MM but think I'm barking up the wrong tree.
Any help would be very much appreciated.

TIA
Click to expand...
Click to expand...
 
Ok, wasn't a personal comment to you - more or less nobody reads posts
anymore.
I'm only on this as I still learn things :)
Steve
 
Back
Top