How to convert hh:mm:ss to 6 hour working day and minutes in excel?

[trinakell]

Hi there,

I work part time and keep a record of any extra hours I do over above my 30 hours a week. I record these in an hh:mm:ss format and have an accumlulated total in the same format. What I now want to do is convert the accumulated total into hh:mm but for a 6 hour day so I know how many days and mins I am owed in lieu. I've used the formula =FLOOR(O4*24/6,1) where O4 is my accumulated hours however it only gives me whole days. This is ok but would prefer to ve able to see it in days and mins if that's possible, and also now curious to see how it could be done! I had originally found this formula =FLOOR(O4*3,1)&" Days and "&TEXT(SUM(O4,-TIME(8,0,0)*FLOOR(O4*3,1))," h") & " Hours " & MINUTE(O4) & " Minutes "& SECOND(O4) & " Seconds" - but realised (I think!) it was calculating for an 8 hour day and despite playing around with it I am not good enough to figure out how to manipulate it to work for me for a 6 hour day! ALl help/ advice/ suggestions gratefully received - I am a novice at this so please bear with me ;)

[#2 · Accepted solution]

@trinakell Not sure what you are trying to achieve. Let's say you have accumulated 21 hours extra time. That would represent 3.5 6-hour working days. And you want this to be displayed as 3 days and 180 minutes?

If so, that could be:

=INT(A1/6)&" days "&TEXT(MOD(A1/6,1)*6/24,"[m]")&" minutes"

where the accumulated extra hours are in A1.

[#3]

@Riny_van_Eekelen yes that's exactly it! Thank you! Or show as 3 days and 3 hours :) I will try the formula now! :)

[#4]

@Riny_van_Eekelen I need to convert estimated project hours into 6 hour working days.  This did the trick.  Thanks for the post!