Time Out of 100 vs 60

%3CLINGO-SUB%20id%3D%22lingo-sub-1823854%22%20slang%3D%22en-US%22%3ETime%20Out%20of%20100%20vs%2060%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1823854%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20looking%20for%20a%20very%20specific%20formula%20to%20solve%20all%20my%20problems.%20The%20program%20my%20work%20uses%20for%20time%20measures%20time%20in%2060%20minute%20ours%20(ex.%205%3A30pm%20closing%20time).%20Whenever%20an%20employee%20clocks%20in%20and%20out%2C%20the%20program%20automatically%20calculates%20their%20hours%20worked%20for%20the%20day.%20At%20the%20end%20of%20the%20pay%20period%2C%20a%20spreadsheet%20is%20auto-made%20that%20has%20everyone's%20worked%20hours%20for%20the%20week%20with%20time%20clocked%20in%20and%20out%2C%20as%20well%20as%20their%20hours%20worked%20in%2060%20minute%20hour%20intervals.%20(ex.%2010%20hours%20and%203%20minutes%20worked%20shows%20as%2010%3A03).%20I%20need%20help%20in%20coming%20up%20with%20a%20formula%20that%20will%20convert%20that%20to%20decimals%20for%20our%20payroll%20program.%20Payroll%20works%20in%20time%20out%20of%20100%2C%20so%205%20hours%20and%2030%20minutes%20of%20work%20is%205.5%20hours%20in%20the%20payroll%20program.%20I%20am%20hoping%20there's%20some%20crazy%20formula%20that%20I%20can%20put%20into%20excel%20to%20turn%20my%205%3A30%20hours%20worked%20to%205.5%20hours%20worked%20for%20payroll.%20Any%20help%20would%20be%20a%20life%20saver%20and%20a%20huge%20time%20saver%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1823854%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1824107%22%20slang%3D%22de-DE%22%3ESubject%3A%20Time%20Out%20of%20100%20vs%2060%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1824107%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847828%22%20target%3D%22_blank%22%3E%40NathanSelvidge%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHere%20is%20an%20old%20work%20of%20mine%2C%20so%20you%20can%20calculate%20the%20time%20in%20decimal%2C%20just%20enter%20the%20start%20and%20end%20time%20of%20the%20work%2C%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EThe%20formula%20for%20that%20was%20this%20one%20down%20there.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EWhere%20U%20was%20intended%20for%20vacation%2C%20K%20for%20sick%2C%20FB%20for%20further%20training.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EYou%20can%20rearrange%20the%20shape%20as%20you%20like%20or%20use%20the%20inserted%20file%20right%20away%2C%20if%20you%20like.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-result-transliteration-container%20result-transliteration-container%20transliteration-container%22%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-result-transliteration-container%20result-transliteration-container%20transliteration-container%22%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%3DIF(OR(I11%3D%22U%22%2CI11%3D%22K%22%2CI11%3D%22FB%22)%2C%24J-2%2CIF(J11%3CI11%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FDIV%3E%3C%2FI11%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1824189%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Out%20of%20100%20vs%2060%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1824189%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847828%22%20target%3D%22_blank%22%3E%40NathanSelvidge%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIrrespective%20of%20whether%20you%20use%20the%20workbook%20offered%2C%20the%20conversion%20form%20a%20'time'%20duration%20(measured%20as%20fractional%20part%20of%20a%20day)%20to%20hours%20(as%20a%20decimal%20number)%20is%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%2024%20*%20time%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hello, I am looking for a very specific formula to solve all my problems. The program my work uses for time measures time in 60 minute ours (ex. 5:30pm closing time). Whenever an employee clocks in and out, the program automatically calculates their hours worked for the day. At the end of the pay period, a spreadsheet is auto-made that has everyone's worked hours for the week with time clocked in and out, as well as their hours worked in 60 minute hour intervals. (ex. 10 hours and 3 minutes worked shows as 10:03). I need help in coming up with a formula that will convert that to decimals for our payroll program. Payroll works in time out of 100, so 5 hours and 30 minutes of work is 5.5 hours in the payroll program. I am hoping there's some crazy formula that I can put into excel to turn my 5:30 hours worked to 5.5 hours worked for payroll. Any help would be a life saver and a huge time saver as well.

2 Replies
Highlighted

@NathanSelvidge 

 

Here is an old work of mine, so you can calculate the time in decimal, just enter the start and end time of the work,
 
The formula for that was this one down there.
Where U was intended for vacation, K for sick, FB for further training.
You can rearrange the shape as you like or use the inserted file right away, if you like.
 
=IF(OR(I11="U",I11="K",I11="FB"),$J$2,IF(J11<I11,((J11+1)-I11)*24,(J11-I11)*24))
 

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Highlighted

@NathanSelvidge 

Irrespective of whether you use the workbook offered, the conversion form a 'time' duration (measured as fractional part of a day) to hours (as a decimal number) is

= 24 * time