Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-21-2024, 06:33 PM
49Lone82 49Lone82 is offline Help with work hours tracking Windows 11 Help with work hours tracking Office 2021
Novice
Help with work hours tracking
 
Join Date: Jun 2024
Posts: 1
49Lone82 is on a distinguished road
Default Help with work hours tracking

Im not real excel savvy. I need to make a spreadsheet that that will track hours on job, hours not worked due to weather delay and have it calculate daily weekly and monthly. Like 10 hours on site, 3 hour rain delay. 7 Total hours working Any help greatly appreciated.
Reply With Quote
  #2  
Old 06-21-2024, 11:32 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Help with work hours tracking Windows 10 Help with work hours tracking Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,945
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Hi and welcome
please post a sample sheet ( no pics) with some data and manually mocked-up results. thanks
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 06-21-2024, 11:56 PM
ArviLaanemets ArviLaanemets is offline Help with work hours tracking Windows 8 Help with work hours tracking Office 2016
Expert
 
Join Date: May 2017
Posts: 950
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

You need to start with creating a defined Calendar Table (tCalendar), placed on some [hidden] sheet - like:
tCalendar: CalendarDate, YYYY (the number of year for CalendarDate, YYYYMM (the number of month of CalendarDate like 202401 for January of current year etc.), YYYYWW (the week number of CalendaryDate), WWMM (The month of YYYYWW in format YYYYMM - you have to decide for rule to which month the week is counted into. E.g. in European countries ISO roles are applied, by which the week belongs to month of it's Thursday.), WdNo (the weekday number of CalendarDate, in range 1 - 7), IsHolyday (1 when the CalendarDate is state holiday, 0 otherwise)
Optionally you can have in tCalendar additional fields like:
IsWD (1 in case WdNo is in range from Monday to Friday, and IsHoliday = 0, and 0 otherwise), NormHrs (number of normative working hours for this date), etc.

You fill the Calendar table for as much of years you think as reasonable.

Additionally you need [hidden] sheets with single-column defined Tables (tMonths and tWeeks) where all month numbers YYYYMM and week numbers YYYYWW used in tCalendar are listed. Dataranges of those columns must be defined as Named Ranges (like lMonth = tMonths[YYYYMM], lWeeks = tWeeks[YYYYWW]), so you can use them as sources for Data Validation Lists (you can't refer to Defined Table columns directly to create a Data Validation List).

Then you need a sheet where you can register working hours (e.g. Worktime). At top of sheet, place Data Validation list where user can select month number in format YYYYMM (I'm explaining how to do for single month, as this is easier. User can archive the workbooks of previous months, and start the new month with empty table.)
Name the cell where the month was selected as Name (like nCurrMonth).

Now you can design your worktime table (put it some rows below the row where user selects the month). Again, I advice to design as Defined table, like
tWorktime:
Date (As you need to get weekly reports too, then the dates for selected month must start with 1st weekday of tCalenadar[WWMM] = nCurrMonth, and end with last weekday of tCalendar[WWMM] = nCurrMonth, and there must be enough rows from prceeding/following months, and you have to enter the info for those dates repeatedly to get the weekly report working properly.),
[WdNo] (optional column for user seeing what day of week this is - use SUMIFS() to get it from tCalendar[WdNo]),
[IsWd] (optional column for user seeing, is the date workday or not - use SUMIFS() to get it from tCalendar[IsWd]),
StartTime (user enters start time, e.g. in format hh:mm),
EndTime (user enters end time, e.g. in format hh:mm),
LunchTime (optionally user enters the length of lunch time, e.g. in format hh:mm),
DelayTime (optionally user enters the length of delay time, e.g. in format hh:mm),
SiteHrs (is calculated as EndTime - StartTime - LunchTime),
WorkHrs (is calculated as SiteHrs - DelayTime,
YYYYMM (needed for monthly report - use SUMIFS() to get it from tCalendar[YYYYMM]),
YYYYWW (needed for weekly report - use SUMIFS() to get it from tCalendar[YYYYWW])

Now create report sheets (MonthlyReport and WeeklyReport)
On MonthlyReport, simply calculate totals (using SUMIFS() again) from Table tWorktime (use time format [h]:mm to get times > 24 hours to be displayed)
On WeeklyReport, create a report Table with headers like W1, W2, .etc., and above it have a range where matching week numbers present in tWorktime are calculated. In every column of report, totals for matching week are calculated similarily with MonthlyReport.

Last edited by ArviLaanemets; 06-22-2024 at 04:44 AM.
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel to Project work hours JHalter Project 0 12-01-2021 10:35 AM
How to assign specific work hours? timo14 Project 1 10-08-2020 02:51 PM
Time Function for Tracking Hours Newto365 OneNote 0 07-11-2016 09:47 AM
Help with work hours tracking Duration & Work Hours Not Close fkistner Project 1 10-31-2015 11:51 AM
Help with work hours tracking Work Week: hours vary each day rkc Outlook 1 08-31-2011 01:17 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft