How do I total Jira time by project?

Export worklogs, convert seconds to hours if needed, take the project key from the issue key, and sum hours per project with SUMIFS or a pivot.

How do I find unbilled hours?

Subtract hours invoiced per project and month from hours logged for the same project and month.

How do I compare time against budget?

Divide logged hours by budgeted hours per project for the period.

How do I convert Jira time from seconds to hours?

Divide by 3,600. Some exports give hours or a formatted duration instead; check the column before converting.

How do I report hours by person and project?

Pivot the time log with person in rows, project in columns and sum of hours as values.

How do I exclude non-billable time?

Add a Billable flag to each worklog and sum only flagged hours with SUMIFS before comparing with invoiced hours.

Why are my billed hours higher than logged hours?

Usually a fixed-fee invoice line entered as hours, or time logged after the export was taken. Separate fixed-fee lines and re-export the time log.

How do I combine Jira time with another timesheet tool?

Stack both exports with the same columns (person, project, date, hours) and a Source column, after mapping each tool's project names to one list of codes.

How do I see who logged time on a closed project?

Join the time log to the project list on project code and filter rows where the project status is closed, then check them with the person who logged the time.

How to match timesheet hours to projects and invoices

By the Analistable team · Updated 2026-10-07 · 6 min read

Export logged time (from Jira worklogs or a timesheet tool) with project, person, date and hours. Sum hours per project and month, then join to the project list for budgeted hours and to invoices for hours billed. Logged minus billed is unbilled work; logged against budget shows which projects are running over.

Part of our guide: How to join spreadsheets on a common column

Roll up the time

Jira exports time spent in seconds on some reports; convert to hours first (=[@[Time spent]] / 3600). Map issue keys to projects with the key's prefix: =TEXTBEFORE([@[Issue key]], "-") turns ACME-142 into ACME.

Logged  =SUMIFS(Time[Hours], Time[Project], [@Project], Time[Month], [@Month])
Billed  =SUMIFS(Invoices[Hours], Invoices[Project], [@Project], Invoices[Month], [@Month])
Unbilled =[@Logged] - [@Billed]

Worked example

September by project
Project	Budget hours (month)	Logged	Billed	Unbilled	Vs budget
ACME	80	92.5	80	12.5	116%
NORD	40	31	31	0	78%
LUNE	60	64	48	16	107%

ACME is over budget and 12.5 hours weren't billed — check whether the contract allows billing overruns. LUNE has 16 unbilled hours within roughly its budget: likely an invoice that missed some timesheets.

People and rates

Join the time log to a rates table (person → cost rate, bill rate) to turn hours into cost and billable value per project: =SUMPRODUCT((Time[Project]=[@Project]) * Time[Hours] * XLOOKUP(Time[Person], Rates[Person], Rates[Bill rate])).

Payroll-side checks (hours paid vs hours worked) are in reconciling payroll with timesheets.

Timesheets from several tools

Teams often log time in more than one place — Jira for engineers, a timesheet tool for consultants. Stack the exports with a Source column and a common set of columns (person, project, date, hours) before rolling up. Map each tool's project names to one list of project codes first, or the same project appears twice.

Checks before invoicing

Hours logged on closed projects — usually a wrong project code.
People logging more than a set number of hours on a single day (often a typo, 80 instead of 8).
Non-billable time (internal meetings, training) excluded from billed hours with a billable flag.
Time logged after the invoice period closed, which belongs on next month's invoice.

Fixing these before the invoice goes out is much easier than issuing credit notes afterwards.

Step by step

Export worklogs for the month with person, issue key or project, date and time spent. Columns and time units vary by tool and report.
Convert time to hours and add Project and Month columns.
Export invoice lines for the same period with project, month and hours billed.
Build a summary with one row per project and month: Logged, Billed, Unbilled and Vs budget.
Review projects over budget or with unbilled hours before the invoice run closes.

TEXTBEFORE needs Excel 365; in Excel 2019 use =LEFT([@[Issue key]], FIND("-", [@[Issue key]]) - 1). In Google Sheets, =REGEXEXTRACT(A2, "^[^-]+") returns the project key, and SUMIFS works as in Excel. Build the Month column with =TEXT([@Date], "yyyy-mm") so it matches the invoice month exactly.

Second example: billable and non-billable time

Not all logged time can be billed. Add a Billable flag (from a project list, an issue label or an account code) and split logged time before comparing with invoices:

Billable logged =SUMIFS(Time[Hours], Time[Project], [@Project], Time[Month], [@Month], Time[Billable], TRUE)
Unbilled        =[@[Billable logged]] - [@Billed]

LUNE, September
Category	Hours
Logged in total	64
Non-billable (internal review, onboarding)	6
Billable logged	58
Billed	48
Unbilled billable hours	10

The 16 unbilled hours in the first table shrink to 10 once internal time is excluded — still worth checking, but a smaller and more accurate figure to chase.

Troubleshooting

When the totals don't agree
Symptom	Likely cause	Fix
Logged hours far too large	Seconds not converted to hours	Divide by 3,600
Hours in the wrong month	Worklog date versus export date, or time zones	Use the worklog's own date column
Project missing from summary	Project code spelled differently across tools	Map names to one project code list
Billed hours greater than logged	Fixed-fee invoice entered as hours, or time logged after export	Separate fixed-fee lines, re-export the time log

To check the result, the sum of Logged across all projects should equal total hours in the time export, and Billed should equal the hours on the month's invoices. Keep the workbook and replace the exports each month. For combining time exports from several tools first, see combining multiple Excel files into one automatically and SUMIFS across multiple sheets.

Doing it in SQL

Logged and billed hours come from different tables, so aggregate each first and join the totals. This runs in DuckDB and SQLite:

WITH logged AS (
  SELECT project, month, SUM(hours) AS logged FROM time_log GROUP BY 1, 2
), billed AS (
  SELECT project, month, SUM(hours) AS billed FROM invoice_lines GROUP BY 1, 2
)
SELECT l.project, l.month, l.logged,
       COALESCE(b.billed, 0) AS billed,
       l.logged - COALESCE(b.billed, 0) AS unbilled
FROM logged l
LEFT JOIN billed b ON b.project = l.project AND b.month = l.month
ORDER BY unbilled DESC;

On the September data above, this returns LUNE first with 16 unbilled hours, then ACME with 12.5 and NORD with 0. Joining the raw tables before summing would repeat each invoice line for every worklog and inflate billed hours.

Common mistakes

Rounding each worklog to the nearest hour before summing, which can shift totals noticeably on busy projects.
Mixing calendar months and billing periods that end mid-month.
Comparing logged hours against a budget for the whole project rather than for the month.
Leaving people's names spelled differently across tools, so the rates lookup misses them.

Repeating it every month

Export worklogs and invoice lines for the month once timesheets are locked.
Paste them into the Time and Invoices tables; the summary updates.
Review projects with unbilled hours with each project manager before invoices are sent.
Record the reason for any hours deliberately written off, so the same project isn't queried again next month.

Over several months, the written-off hours per project tell you which fixed-fee quotes were too low — useful evidence when pricing the next piece of work.

Frequently asked questions

How do I total Jira time by project?: Export worklogs, convert seconds to hours if needed, take the project key from the issue key, and sum hours per project with SUMIFS or a pivot.
How do I find unbilled hours?: Subtract hours invoiced per project and month from hours logged for the same project and month.
How do I compare time against budget?: Divide logged hours by budgeted hours per project for the period.
How do I convert Jira time from seconds to hours?: Divide by 3,600. Some exports give hours or a formatted duration instead; check the column before converting.
How do I report hours by person and project?: Pivot the time log with person in rows, project in columns and sum of hours as values.
How do I exclude non-billable time?: Add a Billable flag to each worklog and sum only flagged hours with SUMIFS before comparing with invoiced hours.
Why are my billed hours higher than logged hours?: Usually a fixed-fee invoice line entered as hours, or time logged after the export was taken. Separate fixed-fee lines and re-export the time log.
How do I combine Jira time with another timesheet tool?: Stack both exports with the same columns (person, project, date, hours) and a Source column, after mapping each tool's project names to one list of codes.
How do I see who logged time on a closed project?: Join the time log to the project list on project code and filter rows where the project status is closed, then check them with the person who logged the time.