Measuring time in Excel

alamedablues

New Member
Joined
Jan 26, 2004
Messages
9
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
Joined
Mar 12, 2002
Messages
11,454
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.
 

alamedablues

New Member
Joined
Jan 26, 2004
Messages
9
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
Joined
Feb 16, 2002
Messages
8,459

ADVERTISEMENT

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
Joined
Jan 26, 2004
Messages
9

ADVERTISEMENT

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
Joined
Jan 26, 2004
Messages
9
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? :oops:
 
Master Excel Bundle

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.

Forum statistics

Threads
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?

Disable AdBlock

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
Top