Hi Mohan Krishna,
//All calculations are based on the decimal representation of date and time
Set vStartTime = .375; //9:00am
Set vCloseTime = .708333333334; // 5:00PM
Set vFullDay = .333333334; // 8 hours - full day
FirstPass:
LOAD CloseDateTime,
StartDateTime,
// calculate total hours worked excluding the Start Day and the Close Day
NetWorkDays(StartDateTime+1,CloseDateTime-1) * 8 as HoursWorked,
//calculates the decimal number for hours after the Start time up to a maximum full days work (9:00am to 5:00pm)
IF(num#(Time#(Frac(StartDateTime),'hh:mm'))>num#($(vStartTime)) and num#(Time#(Frac(StartDateTime),'hh:mm'))>num#($(vCloseTime)) ,0,if(num#(Time#(Frac(StartDateTime),'hh:mm'))>num#($(vStartTime)),num#($(vCloseTime))-num#(Time#(Frac(StartDateTime),'hh:mm')),num#($(vFullDay))))as StartDayTime,
//calculates the decimal number for hours before the Close time up to a maximum full days work (9:00am to 5:00pm)
IF(num#(Time#(Frac(CloseDateTime),'hh:mm'))>num#($(vStartTime)) and num#(Time#(Frac(CloseDateTime),'hh:mm'))>num#($(vCloseTime)) ,num#($(vFullDay)),if(num#(Time#(Frac(CloseDateTime),'hh:mm'))>num#($(vStartTime)),-num#($(vStartTime))+num#(Time#(Frac(CloseDateTime),'hh:mm')),0))as CloseDayTime
FROM
C:\Users\Jarvis\Downloads\NetHoursStartDateCloseDateandTime.xls
(biff, embedded labels, table is Sheet2$);
New:
load
StartDateTime,
CloseDateTime,
HoursWorked,
//We now have 'HoursWorked' which excludes the Start Day and Close Day
//the script below calculates the hrs and mins for those two days
//The first part calculates hrs and min when the start date and close date are the same day
//If the day is Sat or Sun then 0 hours
if(num#(NetWorkDays(StartDateTime,StartDateTime)) = 0 and num#(NetWorkDays(CloseDateTime,CloseDateTime)) = 0,0,
//test if the Start date and Close date are the same
if(Date(floor(StartDateTime)) = Date(floor(CloseDateTime)),
// test to see if the Start time is before 9:00am
if(StartDayTime = num#($(vFullDay)),
//get the hours and minutes up to the close time
Floor(CloseDayTime * 24) & ' Hrs ' & Floor(Frac(CloseDayTime * 24)* 60) & ' Min',
// test to see if the Close time is after 5:00pm
if(CloseDayTime = num#($(vFullDay)),
//get the hours and minutes for the Start day time
Floor(StartDayTime * 24) & ' Hrs ' & Floor(Frac(StartDayTime * 24)* 60) & ' Min',
//If the start time is after 8:00am and close time if before 5:00pm calcuate the hours and minutes
Floor(StartDayTime * 24) + Floor(CloseDayTime * 24)-8 + (Floor((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)) & ' Hrs ' & Frac((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)*60 & ' Min')),
//now calculate hrs and mins when the start date and close date are different days
//if the Start date is Sat or Sun then 0 hrs
if(num#(NetWorkDays(StartDateTime,StartDateTime)) = 0,0,
//get the Start time
Floor(StartDayTime * 24))
//add to the start time the close time
//if the close date is Sat or Sun then hours are zero (0)
+if(num#(NetWorkDays(CloseDateTime,CloseDateTime)) = 0,0,
//calculate the close time hours taking into account the adding start minutes and end minutes can add up to 1 hour
Floor(CloseDayTime * 24)) + HoursWorked+ (Floor((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)) & ' Hrs ' & Frac((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)*60 & ' Min'))
as FinalHours,
// below are some mid calculations that needed to be incorporated in the final answer above
NetWorkDays(StartDateTime,StartDateTime) as StartSatSun,
if(num#(NetWorkDays(StartDateTime,StartDateTime)) = 0,0,Floor(StartDayTime * 24))as SHours, // 0 if the date is a holiday
if(num#(NetWorkDays(CloseDateTime,CloseDateTime)) = 0,0,Floor(CloseDayTime * 24))as CHours, // 0 if the date is a holiday
if(num#(NetWorkDays(StartDateTime,StartDateTime)) = 0,0,Floor(StartDayTime * 24))+if(num#(NetWorkDays(CloseDateTime,CloseDateTime)) = 0,0,Floor(CloseDayTime * 24)) + HoursWorked as SCHours,
(Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60 as SCMinutes,
Floor((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60) as HrsIfMinutesSandCTotalOver1Hour,
Frac((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)*60 as remMinute,
if(num#(NetWorkDays(StartDateTime,StartDateTime)) = 0,0,Floor(StartDayTime * 24))+if(num#(NetWorkDays(CloseDateTime,CloseDateTime)) = 0,0,Floor(CloseDayTime * 24)) + HoursWorked+ (Floor((Floor(Frac(StartDayTime * 24)* 60) + Floor(Frac(CloseDayTime * 24)* 60)) / 60)) as SCHoursFinal,
NetWorkDays(CloseDateTime,CloseDateTime) as CloseSatSun,
Floor(CloseDayTime * 24) as CloseHours,
Floor(Frac(CloseDayTime * 24)* 60) as CloseMinutes,
Frac(CloseDayTime * 24*60 )*60 as CloseSecconds,
(StartDayTime * 24) as StartFirst,
Floor(StartDayTime * 24) as StartHours,
Floor(Frac(StartDayTime * 24)* 60) as StartMinutes,
Frac(StartDayTime * 24*60 )*60 as StartSecconds
Resident FirstPass;
Regards
Hemanth
