The Book-keepers Forum (BKF)
The Book-keepers Forum (UK) -> General Book-keeping & Accounting -> Excel question
Post Info TOPIC: Excel question
Firefly


Member

Status: Offline
Posts: 7
Date:
Excel question
Permalink Closed

Can anyone tell me the formula to convert minutes ie 165 minutes to show as hours and minutes as in 2 hrs 45 minutes not as 2.75 hours or 2 3/4 hours. 

I need to then add a few of these together and for my answer to be in the same format.  confuse

__________________
Quentin Pain


Senior Member

Status: Offline
Posts: 202
Date:
Permalink Closed

Hi Firefly, I don't think you can. The only option I know of would be to program a macro using Visual Basic. That is certainly possible, but not simple.

What you can do is set a format so it shows as Time instead of a number (eg. 2:45 instead of 2.75). Maybe that is all you really need?

__________________

Quentin Pain

Quentin Pain's Marketing Blog
Free Accounting and Bookkeeping Guides
Accounting Software From Accountz
ksalter


Member

Status: Offline
Posts: 8
Date:
Permalink Closed

Yes you can - quite easy if you have hrs and mins in separate columns - email me and I can send you a timesheet which does this.

kevin@bbscomputing.co.uk

www.bbscomputing.co.uk


__________________
K Salter
Wella


Expert

Status: Offline
Posts: 2256
Date:
Permalink Closed

This simple(ish) formula works upto 1439 minutes (24 hours less 1 minute) and puts all data in one cell as text

=TEXT(CONCATENATE(INT(E5/60),":",E5-(INT(E5/60)*60)),"hh:mm").

Change E5 for the cell reference in your spreadsheet.
If you have columns of minutes, you could sum the total minutes at the bottom, and fill the formula down to show all hours & minutes (subject to a total of 1439 minutes)

I have come up with another formula for more than 1439 minutes but it is very complex

Bill

__________________

 

 
Clive


Member

Status: Offline
Posts: 6
Date:
Permalink Closed

Hi,

The below will work without any limitations, hope it helps.

A1 being your original minutes

=INT(A1/60)&"hrs "&MOD(A1,60)&" minutes"

__________________
Wella


Expert

Status: Offline
Posts: 2256
Date:
Permalink Closed

Nice one Clive

Used excel for years and dont think I've ever used the MOD function. a more elegant solution than mine.

I was trying to keep everything columnar. Sure it can be done, with a bit more time (suspect a lot of IF statements are needed)

Bill

__________________

 

 
Page 1 of 1  sorted by
 
Quick Reply

Please log in to post quick replies.
The Book-keepers Forum (UK) -> General Book-keeping & Accounting -> Excel question
Tweet this page Post to Digg Post to Del.icio.us
Members Login
Username 
 
Password 
    Remember Me  
©2007-2024 The Book-keepers Forum (BKF). All Rights Reserved. The Book-keepers Forum (BKF) is a trading division of Bookcert Ltd. Registered in England Company Number 05782923. 2 Laurel House, 1 Station Rd, Worle, Weston-super-Mare, North Somerset, BS22 6AR, United Kingdom. The Book-keepers Forum and BKF are trademarks of Bookcert Ltd. This forum is a discussion forum only. There will usually be more than one opinion to any question and any posting should not be viewed as a definitive solution. No responsibility for loss occasioned to any person acting or refraining from action as a result of any posting on this site is accepted by the contributors or The Book-keepers Forum. In all cases, appropriate professional advice should be sought before making a decision. We reserve the right to remove any postings which are offensive, libellous, self-promoting or engaged in covert marketing. We will not notify users of removals. The views expressed in the forum posts are those of the individual and do not necessary reflect or agree with those of The Book-keepers Forum. Any offensive or unsuitable posts will be removed by the moderators. Any reader of this forum can request for a post to be looked into by sending an email to: bookcertltd@gmail.com.

Privacy & Cookie Policy  About