Ask Search:
Paul CarassPaul Carass 

Calculate Duration at Start of Day and End Of Day from object and its Child Object

Hi All,

I working with a "Time Sheet" Object with a related child object "Time Sheet Entries".
The Time sheet captures our users start and end of day, while the Time Sheet Entries capture Check Ins & Outs of different sites during the day.

I'm looking to calculate the duration from their Start of Day (Time Sheet) from their first Check In (Time Sheet Entry) and the same for the end of day and their last Check Out

User-added image


Any suggestions on how I could best achieve this?

Best Answer chosen by Paul Carass
Angus BrownAngus Brown
If the 'Time Sheet Entries' are linked to the 'Time Sheets' by a 'master-detail' relationship. 

You could do:
  • a roll-up of the Min 'Start Date' of any Time Sheet Entry related to the Time Sheet
  • a roll-up of the Max 'End Date' of any Time Sheet Entry related to the Time Sheet
  • Then use formula fields on the Time Sheet to work out the time between the 'Start of Day' and the new min roll-up field for the earliest Start Date. (And the same for the 'End of Day' and latest 'End Date' which I can see you might need from your screen-shot. 

Someone might have an even cleverer idea about how to achieve this within the report without creating all these custom fields! But I'm not certain if it will be possible to do within the report.

All Answers

Angus BrownAngus Brown
If the 'Time Sheet Entries' are linked to the 'Time Sheets' by a 'master-detail' relationship. 

You could do:
  • a roll-up of the Min 'Start Date' of any Time Sheet Entry related to the Time Sheet
  • a roll-up of the Max 'End Date' of any Time Sheet Entry related to the Time Sheet
  • Then use formula fields on the Time Sheet to work out the time between the 'Start of Day' and the new min roll-up field for the earliest Start Date. (And the same for the 'End of Day' and latest 'End Date' which I can see you might need from your screen-shot. 

Someone might have an even cleverer idea about how to achieve this within the report without creating all these custom fields! But I'm not certain if it will be possible to do within the report.
This was selected as the best answer
Paul CarassPaul Carass
Angus, that was an awesome suggestion. Once you said it, it all clicked with me.
I never even though of the Min & Max function on RollUp Summaries!!
User-added image

Works Perfectly!!
Now I have a few other bits that I add to this. Our service resources get paid at 60% of their normal hourly rate after the first hour of travel, so now I'm able to calculate all that information and take this laborius process away from our poor accounts girl!
Thanks so much for the quick reply
Paul
Angus BrownAngus Brown
Legendary! That's so good to know; another laborious process bites the dust.  

Have a good weekend. Angus