Re calculate Date and Time

[Steven1985]

Column A3 shows Date and Time

30/08/2011 15:07:00

I need in Column AL to replicate that time but change date by 1 day

i.e 31/08/2011 15:07:00

Current formual used is - =IF(A3="","",WORKDAY(A3,1,Controls!$A$10:$A$47)) which changed the date but not the time.

Any ideas?

Thanks

 

[baitmaster]

Assuming your cell value is a number, formatted as an actual date and time, you simply add 1 to it

In Excel, all dates and times are actually stored as numbers, where the date is an integer, and time is a decimal where 1 hour = 1/24 or 0.0417

To see the underlying number, simply format the cell as a number. Don't start trying to swap from time to decimals in your own way, its an absolute minefield and an extremely common mistake! Just think in terms of 1 = 1 day, 0.5 = 12 hours etc

 

[Steven1985]

I need to make it simple for management looking at the data as they wont understand.

So i need it to read 30/08/2011 15:07:00 in Column A and read 31/08/2011 15:07:00 in column AL.

I then have a forumla to count how many days hours and mins it has taken to work.

 

[baitmaster]

I need to make it simple for management looking at the data as they wont understand

well, good luck with that then

Moving swiftly on, I assume that it is indeed a number underneath, that we are working with

You can simply add the 1 as I suggested. You can then also perform other calculations as required, such as subtracting an end time/date from a start time/date

Your results will look unusual though, including the year 1900 or 1904. This is because Excel's date system starts in either 1900 or 1904, depending on version / settings etc. However, your result is actually correct, you just need to play around with it a bit - this year is implying there are no years in your result, as would be expected in the result, say, 10 days. It is usually this type of result that makes people think they are doing something wrong, and yes, it is annoying

Do your calculation, then put INT() around it, to pull out the integer value. This is the number of days, format it as a number as required

Subtract this INT() result from your total days & time, and you are left with just the time. Format this as a time only

Or you can look at custom time / date formats, e.g. [dd"d" hh:mm]

You can also use hour() and minute() functions if needed

 

[Steven1985]

Im sorry but I dont understand.

 

[baitmaster]

calculate with simple addition / subtraction on the times and dates, assuming these are numeric values as described above, and not for example, text strings

use the INT() function on your results, to work out the integer section i.e. the whole number bit before the decimal (this whole number is the number of days taken)

Subtract int(x) from x to leave only the decimal bit, which is effectively the % of a whole day, and is the value that represents a normal time, in Excel time format

Use cell formatting to show your results as you need them. Numbers for the days, and an appropriate time format for the hours and minutes

If you want to show the time differently, you can use =Hour() and =Minute() to break out those sections