Excel Help please.

[Target]

I’m trying to do something that should be simple, but I just can’t get my head round it.
One team divide their time up into blocks of 6 hours. They want something so they can type in the total number of blocks (this changes) say 15, and this will return the answer of 90:00 (90 hours:00 mins) 6 hours multiply by 15 blocks. The 6 hours never change but the number of blocks will. They want it displayed in the HH:MM format.
Then they have a cell with the actual time spent, this again could be anything in HH:MM format, say 75:30.

Then a final cell which shows the difference between the two sets of time.

I feel this should be nice and easy but when I convert to HH:MM I get a blank cell or instead of say 75:30 I get 03:30.
Please tell me how to do this. Hope this makes sense!
Cheers.

[secretcode]

Holy hell. Is this computer assistance day?

Um... what version of Excel are you using?

[Target]

I think it's Excel 2002 SP3.

[Halfshell]

I feel this should be nice and easy but when I convert to HH:MM I get a blank cell or instead of say 75:30 I get 03:30.

Uhm... I can tell you why it's doing that (HH:MM is clock time, rather than value -- 75:30 is half three in the morning, three days hence)... as for the best way around it...

Output it as a straight numerical value of minutes, then extrapolate that into two further cells, one of which displays the minutes, the other hours?

75hrs 30 is 4530 minutes, yeah?

So cellA is the cumulative minutes.

cellB would be "=sum(cellA/60)" giving your hours, displayed to 0dp.
Then something for cell C that extrapolates the decimal point from cellB and works it back into minutes.

I could probably knock something up if I had the spreadsheet in front of me... I'm not so good at working remotely!

[Denyer]

They want it displayed in the HH:MM format.

I think that's your stumbling block. Time display is normally reserved for times, rather than durations.

If they want it easy to visualise, just whack in two columns for data entry -- one for hours, one for minutes...

[Target]

Sounds confusing Halfshell!! :S

Can I send it to you?

[Halfshell]

I probably wouldn't be much help - I'm trying a similar premise with the copy of Excel I've got here at the mo, to no avail. Can't quite get the mix right.

Denyer summarised the problem a bit more concisely than I did.

If I work something out I'll let you know.

[Target]

Cheers!!
I hate working in Time...

[Halfshell]

Haha! I am truly a god amongst men!

Okay.

Output your value as just a flat out number of minutes (being, in the example above, 4530).

For argument's sake, let's say the cell with that number in is A1.

In the next column [B1], you need the formula =ROUND(A1/60-0.5,0)

That'll give you an output of exactly 75 (being the number of hours).

In the subsequent column, enter the formula =SUM((A1/60)-(B1))*60

Which will give you the number of minutes additional to the hours already calculated.

Just substitute A1 and B1 for whatever the cells are in your existing spreadsheet, and copy the formula down the column. Hiding the 4530 column if you so desire.

Shout if you've got any problems.

[Target]

Thanks Halfshell, that's blooming Brilliant!!

Any ideas about the first section, turning the 15 blocks by 6 hours?
I found a formula on the net doing a search, but I don't understand it.
=INT(B23) & "." & INT((B23-INT(B23))*60)
That seems to do it, but only gives me HH:M
But I can live with it!!

[Sixswitch]

Right click on the cell where you want your calculation result and choose 'Format Cell'
In the list on the left, choose 'Custom'
In the box on the right (not the selection box, the box above it) enter: [h]:mm
Click OK

That should sort you out.