Apex Code Development You need to sign in to do that
Don't have an account?
gregj777 Calculating hours using Military time... please help?
I have been a struggling with creating a time sheet app. I want to have the ability for staff to enter Time IN and Time Out hours and then calculate Total hours for that day.
I thought best way would be for them to use a TEXT field and enter time in military hours. ex. 0900.
But now I am stuck on how to go about creating a formula for Total hours.
Ex, Start TIme = 0900
End Time = 1730
End time - Start Time = 8.5 hrs
O.k. got it. See below. Thanks for your guidance. Here's the solution. Althought I don't why salesforce doesn't have a format for just Time.?
CASE( Pay_Type_Mon_wk1__c , "Regular", VALUE(LEFT(End_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(End_Time_Monday_wk1__c, 2))/60) - VALUE(LEFT(Start_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(Start_Time_Monday_wk1__c, 2))/60) -0.5,0) +CASE( Pay_Type_Mon_wk1__c , "Stat", VALUE(LEFT(End_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(End_Time_Monday_wk1__c, 2))/60) - VALUE(LEFT(Start_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(Start_Time_Monday_wk1__c, 2))/60),0) +CASE( Pay_Type_Mon_wk1__c , "Vacation", VALUE(LEFT(End_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(End_Time_Monday_wk1__c, 2))/60) - VALUE(LEFT(Start_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(Start_Time_Monday_wk1__c, 2))/60),0) +CASE( Pay_Type_Mon_wk1__c , "Sick", VALUE(LEFT(End_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(End_Time_Monday_wk1__c, 2))/60) - VALUE(LEFT(Start_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(Start_Time_Monday_wk1__c, 2))/60) -0.5,0)
All Answers
You need to use a VALUE function to convert your Text back to Numbers so that you can perform your mathematic calculation.
Is there any reason why you are not using DateTime fields for this? (it should make the calcuations a lot easier)
I am trying to keep it simple for the user so all they need to do is enter the pay period start and end dates then it calculates the date for example Monday. See below
PayPeriod Start Date: May 29, 2011 {user enters}
PayPeriod End Date: June 10, 2011 {user endters}
Monday: May 30, 2011 {=PayPeriod_Start_Date__c + 1}
TimeIN: 0900 hrs {User enters}
TimeOut: 1730 hrs {user enters}
PayType: Regular {user selects Regular, Stat, Vacation, Sick}
Total Monday Hours: TimeOut - TimeIn {Formula...****this is where I get stuck...so frustrating this isn't easier***}
I started something like this:
CASE( Pay_Type_Mon_wk1__c , "Regular", VALUE(End_Time_Monday_wk1__c) - VALUE(Start_Time_Monday_wk1__c) - 0.5, 0) +
CASE( Pay_Type_Mon_wk1__c , "Stat", VALUE(End_Time_Monday_wk1__c) - VALUE(Start_Time_Monday_wk1__c), 0) +
CASE( Pay_Type_Mon_wk1__c , "Vacation", VALUE(End_Time_Monday_wk1__c) - VALUE(Start_Time_Monday_wk1__c) , 0) +
CASE( Pay_Type_Mon_wk1__c , "Sick", VALUE(End_Time_Monday_wk1__c) - VALUE(Start_Time_Monday_wk1__c) - 0.5, 0)
Can you help me with this please!
Try something like this
Maybe I am confusing you by adding in the PayType. We can take that out for now.
All I want to know is what is the formula to calculate total hrs if:
TImeIN: 0900 Hrs
TimeOut: 1730 Hrs
Total Hrs = 8.5 hrs
Okay, if you need to capture your data in that way (a text field) then you are gonna have to add a few more steps. You're gonna have to parse out the HH and MM from your text field using LEFT and RIGHT functions, and convert your MM value into HH values. For example 30 MM = 0.5 HH, 15 MM = 0.25 HH, etc.
O.k. got it. See below. Thanks for your guidance. Here's the solution. Althought I don't why salesforce doesn't have a format for just Time.?
CASE( Pay_Type_Mon_wk1__c , "Regular", VALUE(LEFT(End_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(End_Time_Monday_wk1__c, 2))/60) - VALUE(LEFT(Start_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(Start_Time_Monday_wk1__c, 2))/60) -0.5,0) +CASE( Pay_Type_Mon_wk1__c , "Stat", VALUE(LEFT(End_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(End_Time_Monday_wk1__c, 2))/60) - VALUE(LEFT(Start_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(Start_Time_Monday_wk1__c, 2))/60),0) +CASE( Pay_Type_Mon_wk1__c , "Vacation", VALUE(LEFT(End_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(End_Time_Monday_wk1__c, 2))/60) - VALUE(LEFT(Start_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(Start_Time_Monday_wk1__c, 2))/60),0) +CASE( Pay_Type_Mon_wk1__c , "Sick", VALUE(LEFT(End_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(End_Time_Monday_wk1__c, 2))/60) - VALUE(LEFT(Start_Time_Monday_wk1__c, 2 ))+(VALUE(RIGHT(Start_Time_Monday_wk1__c, 2))/60) -0.5,0)