Enter Hours and Minutes without colon

[Gretchen]

I've searched for this but can't seem to find the answer. Most of the answers I've found are dealing with actual time of day issues and I really just want to be able to add hours and minutes.

Example -
Task A - 1 hrs 30 min
Task B - 2 hrs 35 min
Task C - 3 hrs 18 min
Total = 7hrs 23 min

Using the formatting [h]:mm I am able to get it to add up correctly as long as I input the data using the colon - but I'd really like to enter the hours and minutes without having to use the colon. I would like to use a period or a dash.

Is there a way to enter hours and minutes without a colon and have it add up to total hours and minutes?

Thanks! Sorry if I'm not wording this correctly!

[protonLeah]

how about this:

[weevie]

I have developed a growing number of spreadsheets that deal with this problem. They will calculate
hh:mm by entering
6.23 and 7.53 and come up with the solution of 14.16 meaning 14hrs and 16mins. They add up all
hrs:mins worked and then ask for an hrly rate and give you the correct answer e.g. 34hrs 48mins
34.48 x hourly rate £15.50 and give the answer of £539.40.

[weevie]

This file will allow you to do this. I plan to create my own site dedicated to these type
of templates as I have numerous of these time-to-money spreadsheets using a decimal point for time that are both English and American which can be formatted to any currency and sell them but could anyone tell me if there Legal Requirements which I don't know about. Also is there any way of protecting spreadsheets to stop people copying them because I can't find one.

Thhankyou

Steve

[protonLeah]

@weevie:
What's the point of posting a password protected workbook with hidden formulas on a HELP FORUM???

You did absolutely nothing to help the OP or any others who might have similar problems in the future. This is not the place to sell your products. Shame.

[weevie]

It was never my intention to sell my product here. I ask if there was a legal requirement whilst selling a spreadsheet and also if there was a way to stop people copying them and I only attached it to prove it could be done. Now if you desperatley want a spreadsheet to do this goto spreadsheetzone (the free site) and look under wages you will find 3 spreadsheets that I have created that have had numerous downloads. If I had wanted to sell it then I wouldn't of come to a place where experts live!

[shg]

Just to be clear, don't post workbooks with workbook protection, sheet protection, or protected VBA projects on this site, please.

Thanks.

[weevie]

THIS DEFINATLEY WON’T BE THE ONLY WAY OR THE MOST ACCURATE WAY TO DO THIS BUT IT IS THE WAY THAT I DO IT

HOW TO ENTER AND CALCULATE TIME USING A DECIMAL NOT A COLON
If your Start time is
6:55 and your finish time is 17:23
To be able to type it in as 6.55 and 17.23 you need to follow these steps
First you need get the 6 out of the number so in a cell c2 type
(Assuming 6.55 is in B2)
=int(b2)
C2 should now say 6
In d2 type in (B2)-C2)
D2 should now = 0 .55
Now C2=6 and (D2)=0.55
What you now need to know is how much 0.55 is of a whole one in decimal language
In E2 you put
=(D2)*100/60 which is
0.55*100/60 = 0.92
(E2)=0.92
In (F2) Type in (C2)+(E2)
(F2) should now have = 6.92
Now you need to do the same for 17.23 assuming you entered it in B3
C3 would be =int(B3)
C3 should now say 17
In D3 Type in (B3)-(C3)
D3 Should Now = 0.23
Now C3=17 D3=0.23
In E3 Put
=(D3)*100/60 which is
0.23*100/60 =0.38
(E3)=0.38
IN (F3) Type in (C3)+(E3)
(F3) should now = 17.38
As a decimal we now know that the start time of 6.55 is 6.92 and finish of 17.23 is 17.38.
We are now free to take one from the other so in G2 put
=(F3)-(F2) 17.38 – 6.92 which is 10.47
So G2=10.47
.We now need it to represent time so in G3 put
=INT(G2)
G3 should now = 10
In G4 put =(G2)-(G3)
G4 should now be 0.47
G4 now needs to be represented as a time not a decimal so you do this in G5
=G4*60/100
G5 should now = 0.28
In G6 you should put G3+G5 which would be
10.28
You Started At 6.55 finished at 17.23 and have worked 10.28

[shg]

Another way:

Please Login or Register  to view this content.

The formula in B4 is =SUMPRODUCT(DOLLARDE(B1:B3/100, 60)) / 24 and the result is formatted as [h]:mm

[martindwilson]

or maybe where b2=17.23 b1=6.55
=TIME(INT(B2),MOD(B2*100,100),0)-TIME(INT(B1),MOD(B1*100,100),0)
=10:28

[royUK]

Weevie

Seems a long winded way of achieving the results, probably 3 or 4 formulas to replace one formula if the entry is correctly entered using colons.

As for the protection it takes seconds to remove and I doubt if you can claim copyright on the spreadsheets.

It was never my intention to sell my product here. I ask if there was a legal requirement whilst selling a spreadsheet and also if there was a way to stop people copying them and I only attached it to prove it could be done. Now if you desperatley want a spreadsheet to do this goto spreadsheetzone (the free site) and look under wages you will find 3 spreadsheets that I have created that have had numerous downloads. If I had wanted to sell it then I wouldn't of come to a place where experts live!

This is hijacking someone else's post.

Chip Pearson's solution is a good VBA way

[weevie]

I will start by saying I am sorry it was never my intention to upset anyone.

In my defence to using those 3 formulas

I was initially asked by my sister to try to find a way of adding up all the hh:mmm worked by all the people at her work and was it possible to get rid of the colon. Now I have no experience of VBA and like many other people my sister got frightened by the notice “This sheet contains Macro’s” she said that she didn’t know if her work would allow it so I had try to discover a formula
I tried numerous formats but quickly realised most of the time formulas did not add hours beyond 24HRS. So it was OK getting to take the later time away from the earlier time but if you wanted to add all your answers together over a week once you got beyond 24HRs it went to back to 0:00. (This may not be true of the solutions others have posted one here because I haven’t tried them but I aren’t that bright as you may all have gathered)
She then asked me if it was possible to calculate a wage based on HH:MMM worked which meant if I converted the decimal straight back to time as soon as I had entered it then you could never multiply it by an hourly rate. This lead me to these three formulas.
They allow me to
1. Enter Time as a Decimal,
2. Add up any amount of hours:Mins Over a year or Ten years if need be
3. They allow me to calculate how much time people have worked with upto minute accuracy.
4. They allow me to take this figure and multiply it by an hourly rate and then and give me a wage based on HH and MINS not just HRS.

I realise this may not be of use to anyone else but it did help my sister at the time

[shg]

once you got beyond 24HRs it went to back to 0:00

If you format the results as [h]:mm it will show all the hours.

[weevie]

Yeah eventually found out that using [h]:mmm added up all hours and minutes bu that didn't help me calculate how much someone was owed based on time worked because if you try typing in 122:45 at £30.00 per hour it will not reconise both

[shg]

With 122:45 in A1 and 30 in A2

=24*A1*A2 returns 3682.50