Hours worked - Military Time - Expressed as a decimal...

[Tom Schreiner]

Hi
My time is in four digit format with no colon.
0600
0715
2345
ect...

The value I am trying to get is hours worked expressed as a decimal...

For example:

2315 starting time
0730 ending time

Formula would display:
8.25

I know beggars can't be choosers, but I would prefer an answer in VBA, but will gladly take an excel in the cell answer!

Thanks!

[Aladin Akyurek]

On 2002-03-23 05:25, TsTom wrote:
Hi
My time is in four digit format with no colon.
0600
0715
2345
ect...

The value I am trying to get is hours worked expressed as a decimal...

For example:

2315 starting time
0730 ending time

Formula would display:
8.25

I know beggars can't be choosers, but I would prefer an answer in VBA, but will gladly take an excel in the cell answer!

Thanks!

If your text-formatted times are always 4-digits, the following would do what you want.

in C2 enter: =(LEFT(A2,2)&":"&RIGHT(A2,2))+0
in D2 enter: =(LEFT(B2,2)&":"&RIGHT(B2,2))+0

in E2 enter: =(D2+(D2
where A2 houses a 4-digit start time and B2 a 4-digit end time.

You could also, if desired, plug in C2 and D2 in the formula of E2.

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-03-23 05:55 ]

[Tom Schreiner]

2315 0730 = 8.25
0730 1500 = 7.50
0800 1720 = 9.33
2359 0059 = 1.00
0001 2301 = 23.00
1550 1450 = 23.00

All perfect!
Thanks Aladin!

Would you walk another mile with me for two more questions?

in C2 enter: =(LEFT(A2,2)&":"&RIGHT(A2,2))+0
in D2 enter: =(LEFT(B2,2)&":"&RIGHT(B2,2))+0
in E2 enter: =(D2+(D2

Why the +0 on the ends of the first two?
I do not understand the third???
Especially (D2 Thanks. I appreciate and hope that I might return the favor someday. Though looking at your posts, it might be a while!


[ This Message was edited by: TsTom on 2002-03-23 06:17 ]

[Aladin Akyurek]

On 2002-03-23 06:16, TsTom wrote:
2315 0730 = 8.25
0730 1500 = 7.50
0800 1720 = 9.33
2359 0059 = 1.00
0001 2301 = 23.00
1550 1450 = 23.00

All perfect!
Thanks Aladin!

Would you walk another mile with me for two more questions?

in C2 enter: =(LEFT(A2,2)&":"&RIGHT(A2,2))+0
in D2 enter: =(LEFT(B2,2)&":"&RIGHT(B2,2))+0
in E2 enter: =(D2+(D2

Why the +0 on the ends of the first two?
I do not understand the third???
Especially (D2 Thanks. I appreciate and hope that I might return the favor someday. Though looking at your posts, it might be a while!

[ This Message was edited by: TsTom on 2002-03-23 06:17 ]

+0 coerces (forces) Excel to treat here the result of the formula as number. The existence of ":" makes Excel to recognize the result as time.

(D2
07:30 is represented internally as 0.3125.
23:15 as 0.96875.

1.3125 - 0.96875 = 0.34375,

which is the internal value of 8:15.

8:15*24 converts 8:15 to decimal hours.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-03-23 06:49 ]

[Tom Schreiner]

Thanks again Aladin.
Tis in me notes.
Have a good day!

[Yogi Anand]

Hi Aladin:
Following up with a notes of THANKS from TsTom on your very insightful postings, I have a comment/question for you.
Incorporation of D2 minus C2 in the formula adds tremendous value ... but naturally it will be limited to a 24 hour cycle.
If I go beyond the 24 hour cycle ... say I start working on 3/23/02 11:30 and stop work on 3/25/02 7:30, I have no choice but to include the date along with the time, and follow up with normal subtraction. I don't know if it is even relevant here but I thought I might mention it anyway.
Regards!

_________________
Yogi Anand
Edit: Deleted reference to inactive web site from signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 18:48 ]

[Brian from Maui]

Aloha,

I posted this question yesterday, and I'll try again. Our company deals in military time also. We (not personally) keeps a log, and one of the major complaints is inputting the colon. Using the custom format 00:00, they can't add/subtract times. Is it possible for a macro to add the colons and still be able to add/subtract. I'm not looking for someone to write the code, just if it's possible.

Brian

[Jack in the UK]

Hi Brian

ermm.. yep VBA im sure more of lett anything can be done this wioll be none to complex for a proper programmer, so input time as 6:26 or however and then VBA can convert to whatever you want. the subtaaction part is a case of making the time usable in math (Hey an American word) so you can calculate with it, this lies the problem.

VBA would add columns convert as many times as needed and remover the now redundant columns used in convertion...

thats the idea...

You can in Excel use custom functions, know mostly as UDF (user definded function) say =SUM(... this might be =JACK( notice JACK in not in excel so we write a function to do WHATEVER we like... normal to convert data not so much math it.

OK UDF are VERY VERY VERY slow, as they are normally set to target or range settings and so will be a nasty little thing to speed up.

Saying that i have a few, if you like i can email you, bounce me a mail to work to remind me and ill set a sheet with some goodies in, convertions most or add something and remove something...

I find in Excel most this can be done of this nature in open play.. Aladin the formula mistro has been posting an even without checking and testing his work i know it perfection, and a good start.

If i was asked to make VBA in this i would convert Adadins answer in to a UDF or command button and use range settings, this would give cell or row/column of cleaned data...

i would not deviate from Aladins answers as i know form nearly 2 years of trying to beat this answers its pointless.

Its worth adding that fast codes against efficent codes and simple scripts do not mean they are the BEST.. i honest beleive its about exceptable resultys and this includes time.

I have Script taht adds col converts add something the the string and copies and pastes as value and delets the original col ... how fast???

OK 65500 cells in less than a second.

IN UDF that same codes take????

As long as it takes to copy paste and calculate... 5 seconds//////

Does it matter?

to me depends how i feel....... i happen to like a pop up saying a message to lost pals friends that have died so i remember them, or i love to type =Jack_Convert(

just me.

Take care friend...

[Yogi Anand]

On 2002-03-23 09:59, Brian from Maui wrote:
Aloha,

I posted this question yesterday, and I'll try again. Our company deals in military time also. We (not personally) keeps a log, and one of the major complaints is inputting the colon. Using the custom format 00:00, they can't add/subtract times. Is it possible for a macro to add the colons and still be able to add/subtract. I'm not looking for someone to write the code, just if it's possible.

Brian

Hi Brian:
We discussed this in yesterday's post that started with CJ, and we looked at a number of ways of doing this including using the custom format as you stated here. The custom format 00:00 works with numbers only, so there is a problem applying it with times with trailing 0's ... in which case there will be only 1 digit in the hours. So that will have to be taken into account.
However, as discussed at the tailend of yesterday's thread, you can convert your time entry '0830 into time by
'0830 =time(left(a2,2),right(a2,2),0))
and then you can use the new entry to add / subtract, or whatever.
If I had '0830 in cell A2, and '0600 in B2
'0830 - '0600 in cell C2 ... result formula
=time(left(a2,2),right(a2,2),0)-time(left(b2,2),right(b2,2),0)
=2:30:00
then if you want to format this as hours in decimal notation
=(a2-b2)/24 formatted as NUMBER|General gives
=2.5

HTH
Please post back if it works for you ... otherwise explain a little further and let us take it from there!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard coded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 15:49 ]