How to do do this in Excel.

casanova · 27-09-2007, 05:53 PM

It is quite easy to subtract dates and times in Excel but it doesn't always show reality.

E.g Lets say that some1 is working on a project. He works every weekday from 10am-5pm. Lets say that the person starts working on 1st Jan and ends the project on 31 Jan. Now Excel will show the effort to be 31 Days (almost) whereas the reality is different. It would be something like 22 (no. of weekdays) * 7 (hours) hours.

So how can we achieve something like this.

pillainp · 27-09-2007, 06:55 PM

You can use the NETWORKDAYS function for this type of calculation:

Cell A1: Start Date
Cell A2: End Date
Cell A3: Hours worked per day
Cell A4: =NETWORKDAYS(A1,A2)*A3 will give you the total hours worked by this person, counting only weekdays.

skghosh44 · 27-09-2007, 08:22 PM

As the question here about date Calculation, hence I wish to ask a question here. If I format a cell for british date (dd/mm/yyyy) exell accept it, but after some days some of the date which are 1-12 got automatically changed to american date format. I cant understand the problem why this is happening can any body point out the problem.

Another question it may be a simple question, but I am unable to do it. In the british date format how to find out the days by substracting between the date.(both days inclusive)

casanova · 27-09-2007, 11:28 PM

@pillainp

I know about NETWORKDAYS. I came across through help in excel. It does count only weekdays, but there is an issue

Lets say, if one cell contains the value 12/9/2007 11:00 AM and next cell contain 12/9/2007 11:30 AM or 14/9/2007 5

M

If I use NETWORKDAYS, I would get the result 1 or 2 and if I multiply it with 24, I would get 24 or 48. However; the real results would be .25 or 13 hours. I am adding this to the example mentioned above. Might be some complex formula along with NETWORKDAYS would help, but I don't know much about excel.

pillainp · 27-09-2007, 11:29 PM

Excel basically treats all dates as an indexed series of numbers. So no matter how you set the date (ie. British or US format), it will treat each date as a number incremented from 01/01/1900 (on Windows), with that date being assigned the value 1. Each full day also carries the value 1.

If you want to see the numerical value of a date in Excel, just type it into a cell, press ENTER to exit edit mode, and then press CTRL+~. This is a toggle, pressing it again will display the date.
So you can do addition and subtraction between dates (or use their numerical values instead. Just add 1 to the result to get the result with both dates inclusive.

skghosh44 · 28-09-2007, 08:40 AM

If I enter as
25/09/2007 in Cell A1 and 27/09/2007 in Cell B1 and the the cell is formated as UK date format now how I will get the result 4 in cell C1.

If I type in Cell A3 as +(a2-a1)+1 the result displays as #VALUE i.e text.

Ramakrishnan · 28-09-2007, 03:24 PM

@skghosh44

If we do as posted by you, we get something like this 02/01/1900 00:00
format it to numbers. You will get 3.

Before doing the above, you may make sure that in the control panel>regional settings the date format is set to dd/mm/yyyy

Hope this solves your problem.

skghosh44 · 28-09-2007, 09:53 PM

Quote:

Originally Posted by Ramakrishnan

@skghosh44

If we do as posted by you, we get something like this 02/01/1900 00:00
format it to numbers. You will get 3.

Before doing the above, you may make sure that in the control panel>regional settings the date format is set to dd/mm/yyyy

Hope this solves your problem.

I have change the regional settings as English(united kingdom)
and changed the enter date to number format but the result is same as 02/01/1900 00:00.
Will u pl show me how u get the result 3.