Sunday, October 13, 2013

yogi_Split Accumulated Hours Worked Into Days Hours Minutes Seconds Based On Specified Amount Of Time Worked Per Day

                                          Google Spreadsheet   Post  #1395
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Oct 13, 2013
question by: Hamish Whitall (!category-topic/docs/spreadsheets/_GisKBhdAnA)
Spreadsheet cell has format Hours (HH:MM:SS) and I want to convert it to days, hours and minutes
Hi there,

I have a cell which has added HH:MM:SS values (I'm adding up working hours in a week). I want to use Google App Script to convert this to days, hours and minutes. It seems like if I do something like:

  dhm = hms.toString().split(":")

it does not work.

If I look at what's being passed into my function, It's a DATETIME, so for example 28:15:00 (28h, 15 minutes) actually translates into Sunday 31 December 1899, 14:15. But that's not what I'm after. I want to split the 28h into 3 days (given that there's 8.5h in a working day - for those of us not blessed to be on call), then some hours and perhaps some minutes.

So how does one convert this Hours format into something that I want?

Suggestions on how to do this?


following is a formula baed solution to a bit more generalized problem