# Measuring time in Excel

#### alamedablues

##### New Member
Heres the scenario,

A major part of my work is to measure time. The time it takes for someone to receive health treatment or, how long the person had to wait for services, as well as how many days a person spent in treatment so that I can attached a dollar amount per day to both of these time periods.

Problem:

In the spreadsheets I'm developing there are three columns next to each other. One is "Waiting Start Date," next to that is, "Waiting End Date," and adjacent to that is "Number of Days Waiting." All of the cells in the date columns are always formatted to a date format such as January 21, 2004. The "Number of Days Waiting" column is always formatted to a number. The function I use that is not sufficient for this process is, "Waiting End Date" minus "Waiting Start Date" equals "Number of Days Waiting." Such as, =D2-C2.

This formula will only calculate number of days waiting when a Waiting End Date-DATE is typed in the cell to the right of "Waiting Start Date."

What I need is to have the days waiting begin to calculate on its own after a start date with the end date cell left open so that each day the number of days waiting changes to the next number of days almost like an automobile odometer. (I hope this explains it well.) The clock needs to shut off once a Waiting End Date DATE is typed in. This same calculation is need for "Number of Days in Tx" as well. It is that number I'll attached a dollar sign to.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Yogi Anand

##### MrExcel MVP
Hi alamedablues:

Welcome to MrExcel Board!

If I have understood you correctly, let us have a look at ...
Book1
BCDEF
1WaitingStartDateWaitingEndDateNumberOfDaysWaiting
2January21,20045
3January22,20044
4January23,20043
5January24,20042
Sheet16

Is this what you are trying to accomplish -- if not please explain alittle further and then let us take it from there.

#### SIXTH SENSE

##### Well-known Member
hi!
try this
Book1
ABCD
1startendno.ofdays
21/1/20041/5/20044
31/5/200422
Sheet1

#### alamedablues

##### New Member
Both of these options are supurb and a lot further along that I've gotten. Thank you so very much!

SIXTH SENSE's calulation works fine. Is it possible to have th eNo of days cell "Zero Out" meaning, contain a 0 if there are no dates in the START and END date columms?

If not, then again thanks a bunch I really appreciate it

#### Brian from Maui

##### MrExcel MVP

alamedablues said:
Both of these options are supurb and a lot further along that I've gotten. Thank you so very much!

SIXTH SENSE's calulation works fine. Is it possible to have th eNo of days cell "Zero Out" meaning, contain a 0 if there are no dates in the START and END date columms?

If not, then again thanks a bunch I really appreciate it

Assuming there can't be an end date if not start date,

=IF(A2="",0,IF(B2,B2-A2,TODAY()-A2))

#### alamedablues

##### New Member
Brian from Maui, this works perfectly! Thanks to everyone for your help

#### alamedablues

##### New Member

hey this is alamedablues again. I need to develp employee time sheets that measure hours 1/2hour and minutes, what would be the formula for this?

EXAMPLE:

Time In________Time Out________Total Hours Worked

Any ideas?

#### alamedablues

##### New Member
hey this is alamedablues again. I need to develp employee time sheets that measure hours 1/2hour and minutes, what would be the formula for this?

EXAMPLE:

Time In________Time Out________Total Hours Worked

Any ideas?

Replies
0
Views
207
Replies
6
Views
294
Replies
0
Views
133
Replies
15
Views
152
Replies
24
Views
2K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,094
Messages
5,768,051
Members
425,451
Latest member
JohnBrooksBiddle

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back