Forum Discussion
Timespan in one cell
At my workplace I was asked to prepare an Excel sheet that will be used for workshift planning. It us hoped that people insert the workshift timespan, such as "8-16" (meaning starting 8am and ending 4pm), into one cell. The Excel is meant to count the total working hours for every person so that every person knows when he/she has planned enough shifts in one list (4-week period).
The problem:
I know how Excel can count working hours in simple cases such as when a person inserts "8-16", for example using reciprocal to 8 minus 16. The problem for me arises, when the digits are not on the hours, such as 10-19.30.
Can anyone help? I could not find solution by googling or so.
There of course would not be any problem if the starting and ending times would be put in separate cells. But at this point I was asked to prepare an Excel sheet in which starting and ending times are inserted into same cell as timespan.
Thanks.
I strongly suggest that you try to convince the powers that be that using two separate cells is much, much better. Not only does it make calculating working hours a lot easier, it also helps preventing errors.
If you enter an invalid time in a cell, the result will be an error or a value different from what was intended. But since a value such as 10-19:30 is text, you can enter anything. Moreover, Excel tends to interpret a value such as 8-11 as a date (August 11 or November 8, depending on your regional settings).
2 Replies
I strongly suggest that you try to convince the powers that be that using two separate cells is much, much better. Not only does it make calculating working hours a lot easier, it also helps preventing errors.
If you enter an invalid time in a cell, the result will be an error or a value different from what was intended. But since a value such as 10-19:30 is text, you can enter anything. Moreover, Excel tends to interpret a value such as 8-11 as a date (August 11 or November 8, depending on your regional settings).
- Thanks a lot Hans and sorry for the delay in reacting. Yes, you are absolutely right. I ended up using letter codes (the shifts are almost always on same hours such as 14-22) and if-function. So fat seems to work well. Thanks.
