Google-sheets – How to output end task time base on start time, duration and working time in Google Sheets

formulasgoogle sheetsgoogle-sheets-dates

I'm using a spreadsheet to perform calculations and I'd like to make a task management file. For example:

Working Time: 8:00 ~ 17:00
Lunch Break: 12:00 ~ 13:00

+- Start Time -+- Duration -+- End Time  -+
|  2/24 08:30 -+-   2:30   -+- 2/24 11:30 |
|  2/24 11:30 -+-   4:00   -+- 2/24 16:30 |
|  2/24 16:30 -+-   2:30   -+- 2/25 10:00 |

Does anyone know if this is possible? I want to calculate "End Time".

Best Answer

=A2+$E$1+$E$2 is same like: =A2+TIME(8,0,0)+TIME(1,0,0)

=A2+B2

=C9-B9 - total lunch time
=E9-A9-D9 - total worked hours

=A16+F16+D16 - your end time

demo spreadsheet

Related Solutions

Google-sheets – How to add days to a date

You can add days to a date in a Google spreadsheet like this:

DATE(2010, 12, 01) + 5

Works with TODAY() as well:

TODAY() + 7

Google-sheets – How to extract information from the subject line and add to Google Sheets

As long as you don't need to parse the contents of the subject line, you can do this with If This Then That.

You'll need the Gmail and Google Drive channels. Specifically:

Trigger

Depending on how you're going to identify the incoming message, you'll use one of the following triggers:

Any new email in inbox
New email in inbox from
New starred email in inbox
New email in inbox labeled
New email in inbox from search

"Subject" is one of the ingredients available, which you'll obviously need. There's other useful things, like "ReceivedAt".

Action

You'll want Add row to spreadsheet where you'll defined the name of the spreadsheet, what goes in the row, and the folder path in Google Drive where it's located.

There are several recipes already shared that might service. This recipe, for instance, saves a new row for every received message.