Time minus 07:30

I'm building a hour counting schema where you enter start time, end time, lunch and get a result of how long you have been at work.

Norman work day is 7 hours 30 minutes. I want to calculate the diffrence between my time at the office and 7:30.

I have tried this formula: =G16-7:30 It gives me #VALUE!

edit retag close merge delete

Next to no use for the forum purposes.

( 2017-10-24 15:23:59 +0200 )edit

Sort by » oldest newest most voted

The colon notation is only recognized when new data is entered in a cell.
To get it converted to internal time format in a formula you have to use the conversion function TIMEVALUE() where the parameter is a string (text) giving the time in a recognized format.
=G16-TIMEVALUE("7:30") should do.
I would, however, suggest to enter the time constant (7:30) into a dedicated cell, say D2, and to reference it like in
=G16-$D$2 then.
Constants in formulae (except an index or an offset or something internally functional in a similar way) are mostly bad design. Consider the complications concerning maintenance or enhancemets.

more

Thanks, that worked!

( 2017-10-26 07:29:57 +0200 )edit

Glad to hear this. In specific cases it may, however, be useful to resort to automatic conversion as @pierre-yves samyn suggested. I didn't think of that first, and considering it again I don' like it exactly. An exotic reason concerning time values: Using the given format it will not work in the locale 'Italian (Italy)'. I don't like to depend on some stubborn mannerisms of locales.
In fact my suggestion to use TIMEVALUE doesn't cure the desease. Only TIME(7;30;0) does.

( 2017-10-26 10:10:26 +0200 )edit

In some specific cases automatic conversion tries to be "smart" by combining (primary) syntactical and (secondary) semantical factors for the judgement what result type of conversion (if any) should apply. Horror!

( 2017-10-26 10:15:51 +0200 )edit

Hi

@Lupp +1 for not using constant in formula (It is better to enter in a cell as he wrote it).

Nevertheless, for information, this entry is allowed: =G16-"07:30"

Regards

more