Rounding Time down

[Vacuum Sealed]

Hi all

Require help with the best way to structure a Script to round down time to
the nearest even 30 mins.

eg
10:15 = 10:00
14:45 = 14:30
etc.

Though
10:00 would still = 10:00 and
14:30 would still = 14:30

The time is always recorded in 15 min increments, but for this specific
report required, I have to covert it to the 30 min format.

I figured VB code would be the better option as an IF() statement would
possibly be a tad too long.

TIA
Mick

 

[Alan]

Hi all

Require help with the best way to structure a Script to round down time to
the nearest even 30 mins.

eg
10:15 = 10:00
14:45 = 14:30
etc.

Though
10:00 would still = 10:00 and
14:30 would still = 14:30

The time is always recorded in 15 min increments, but for this specific
report required, I have to covert it to the 30 min format.

I figured VB code would be the better option as an IF() statement would
possibly be a tad too long.

TIA
Mick

On the basis that your time is always recorded in 15 minute
increments:

=IF(MINUTE(A1)=15,A1-0.0104166666666666,IF(MINUTE(A1)=45,A1-0.0104166666666666,A1))

assuming your input time is in cell A1. You may have to format the
target cell as hh:mm:ss and the 15 minute constant (0.01041 .....)
could be stored in another cell (eg A2) such that the if statement
simplifies to

=IF(MINUTE(A1)=15,A1-$A$2,IF(MINUTE(A1)=45,A1-$A$2,A1))

A.

 

[Alan]

On the basis that your time is always recorded in 15 minute
increments:

=IF(MINUTE(A1)=15,A1-0.0104166666666666,IF(MINUTE(A1)=45,A1-0.0104166666666­666,A1))

assuming your input time is in cell A1. You may have to format the
target cell as hh:mm:ss and the 15 minute constant (0.01041 .....)
could be stored in another cell (eg A2) such that the if statement
simplifies to

=IF(MINUTE(A1)=15,A1-$A$2,IF(MINUTE(A1)=45,A1-$A$2,A1))

A.- Hide quoted text -

- Show quoted text -

Alternatively:

=A1-$A$2*OR(MINUTE(A1)=15,MINUTE(A1)=45)

Same input cell addresses as in previous response.

 

[Vacuum Sealed]

Alan

Thank you so much

That works magnificantly.

Cheers
Mick

 

[joeu2004]

Require help with the best way to structure a Script
to round down time to the nearest even 30 mins.
eg
10:15 = 10:00
14:45 = 14:30
etc.
Though
10:00 would still = 10:00 and
14:30 would still = 14:30
The time is always recorded in 15 min increments

It might help to know that numeric time is stored as a fraction of a
day. 1 hour is 1/24; 1 minute is 1/1440 (1440 = 24*60); and 1 second
is 1/86400 (86400 = 24*60*60).

So rather than taking advantage of the special assumption that time is
entered in 15-min increments, the more general way to round down to 30-
min increments is:

=FLOOR(A1*1440,30)/1440

formatted in the form you wish.