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: Pasteboard - Uploaded Image
Your attachment is not a spreadsheet(-document), but an image.
Next to no use for the forum purposes.
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
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.
Thanks, that worked!
Glad to hear this. In specific cases it may, however, be useful to resort to automatic conversion as @PYS 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.
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!
