Ask Your Question

Time minus 07:30

asked 2017-10-24 11:46:37 +0200

solo gravatar image

updated 2017-10-24 11:46:48 +0200

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!

My spreadsheet:

edit retag flag offensive close merge delete


Your attachment is not a spreadsheet(-document), but an image.
Next to no use for the forum purposes.

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

2 Answers

Sort by » oldest newest most voted

answered 2017-10-24 15:20:29 +0200

Lupp gravatar image

updated 2017-10-24 15:26:53 +0200

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.

edit flag offensive delete link more


Thanks, that worked!

solo gravatar imagesolo ( 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.

Lupp gravatar imageLupp ( 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!

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

answered 2017-10-24 19:31:55 +0200

pierre-yves samyn gravatar image


@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"


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-10-24 11:46:37 +0200

Seen: 2,097 times

Last updated: Oct 24 '17