When SO means 10 hours

Occasional Visitor

I have a inherited spreadsheet which seems to have no formulas, but basically the spreadsheet is to calculate annual leave - in hours - but also has "SO" (sleepover) which equates to 10 hours.  They do not wish to substitute SO for 10 as they need to see when someone should have been present for a SO, so it needs to be shown as "SO" on the spreadsheet but needs to be calculable at 10 hours in a range of multiple cells to cover the full annual leave year.


4 Replies



It would help us be specific in giving suggestions or solutions if you could give a more complete description of how the data are arrayed, i.e., in particular the rest of that "range of multiple cells" that "cover the full annual leave year"


For example, Is this a single table with rows for each day and how many hours in each day, with some of those being coded as SO? Or what?


Ideally, please post a copy of the spreadsheet, if not here, in GoogleSheets or OneDrive, granting access to us to view and modify. Second best: an image or screen grab. Just make sure that there are no names of real people, or any other identifiable data elements.

@Lynne_McWhinnie Well, you don't really give a way much, but perhaps the picture below will help you on your way. If not, come back with more clarity about how your actual sheets looks like. Perhaps a screenshot or share a file via OneDrive, Dropbox etc.

Screenshot 2022-04-25 at 16.44.42.png

Oops: Noticed @mathetes reply after I posted mine.



Does this help you? Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. 


Practically the same as above

=SUMPRODUCT((range="SO")*10) + SUM(range)