| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding on a 24 hour clock basis
Hello all, i am sure this is very simple but.....
I am trying to add 3 working days to a date and time and have the answer displayed in dd/mmm hr:mmm format so if i add 3 working days to 29/6/6 9:00 i would have the answer retrned as 3-Jul 9:00 Thanks for your guidence Suddes |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding on a 24 hour clock basis
=workday(A1,3)
you can also exclude holiday dates by putting them in a range, say M1:M10, and using =workday(A1,3,M1:M10) Workday is part of the Analysis Toolpak addin, so that has to be installed (ToolsAddins) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Suddes" wrote in message ... Hello all, i am sure this is very simple but..... I am trying to add 3 working days to a date and time and have the answer displayed in dd/mmm hr:mmm format so if i add 3 working days to 29/6/6 9:00 i would have the answer retrned as 3-Jul 9:00 Thanks for your guidence Suddes |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding on a 24 hour clock basis
Hi Suddes,
29/6/6 + 3 working days gives 4-Jul, because the following three working days are 30/6, 3/7, 4/7 being 1/7, 2/7 Saturday and Sunday. The formula is (if the date to be increased is in A1) : =WORKDAY(A1,3) Format the result cell like dd-mmm h:mm Regards, Stefi €žSuddes€ť ezt Ă*rta: Hello all, i am sure this is very simple but..... I am trying to add 3 working days to a date and time and have the answer displayed in dd/mmm hr:mmm format so if i add 3 working days to 29/6/6 9:00 i would have the answer retrned as 3-Jul 9:00 Thanks for your guidence Suddes |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding on a 24 hour clock basis
Hi Stefi
I tried this and changed the cell format but the result dse not display the time part ie 29/6/6 09:00:00 plus 3 working days should return 04/7/6 09:00:00 but i get 04/07 00:00 it does not seem to be looking at the hours?! any advice Thanks "Stefi" wrote: Hi Suddes, 29/6/6 + 3 working days gives 4-Jul, because the following three working days are 30/6, 3/7, 4/7 being 1/7, 2/7 Saturday and Sunday. The formula is (if the date to be increased is in A1) : =WORKDAY(A1,3) Format the result cell like dd-mmm h:mm Regards, Stefi €žSuddes€ť ezt Ă*rta: Hello all, i am sure this is very simple but..... I am trying to add 3 working days to a date and time and have the answer displayed in dd/mmm hr:mmm format so if i add 3 working days to 29/6/6 9:00 i would have the answer retrned as 3-Jul 9:00 Thanks for your guidence Suddes |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding on a 24 hour clock basis
Sorry,
=WORKDAY(F1,3)+F1-INT(F1) This works! Stefi €žSuddes€ť ezt Ă*rta: Hi Stefi I tried this and changed the cell format but the result dse not display the time part ie 29/6/6 09:00:00 plus 3 working days should return 04/7/6 09:00:00 but i get 04/07 00:00 it does not seem to be looking at the hours?! any advice Thanks "Stefi" wrote: Hi Suddes, 29/6/6 + 3 working days gives 4-Jul, because the following three working days are 30/6, 3/7, 4/7 being 1/7, 2/7 Saturday and Sunday. The formula is (if the date to be increased is in A1) : =WORKDAY(A1,3) Format the result cell like dd-mmm h:mm Regards, Stefi €žSuddes€ť ezt Ă*rta: Hello all, i am sure this is very simple but..... I am trying to add 3 working days to a date and time and have the answer displayed in dd/mmm hr:mmm format so if i add 3 working days to 29/6/6 9:00 i would have the answer retrned as 3-Jul 9:00 Thanks for your guidence Suddes |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding on a 24 hour clock basis
To be consequent:
=WORKDAY(A1,3)+A1-INT(A1) Stefi €žSuddes€ť ezt Ă*rta: Hi Stefi I tried this and changed the cell format but the result dse not display the time part ie 29/6/6 09:00:00 plus 3 working days should return 04/7/6 09:00:00 but i get 04/07 00:00 it does not seem to be looking at the hours?! any advice Thanks "Stefi" wrote: Hi Suddes, 29/6/6 + 3 working days gives 4-Jul, because the following three working days are 30/6, 3/7, 4/7 being 1/7, 2/7 Saturday and Sunday. The formula is (if the date to be increased is in A1) : =WORKDAY(A1,3) Format the result cell like dd-mmm h:mm Regards, Stefi €žSuddes€ť ezt Ă*rta: Hello all, i am sure this is very simple but..... I am trying to add 3 working days to a date and time and have the answer displayed in dd/mmm hr:mmm format so if i add 3 working days to 29/6/6 9:00 i would have the answer retrned as 3-Jul 9:00 Thanks for your guidence Suddes |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding on a 24 hour clock basis
How silly. Try this
=WORKDAY(A1,3)+MOD(A1,1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Suddes" wrote in message ... Hi Stefi I tried this and changed the cell format but the result dse not display the time part ie 29/6/6 09:00:00 plus 3 working days should return 04/7/6 09:00:00 but i get 04/07 00:00 it does not seem to be looking at the hours?! any advice Thanks "Stefi" wrote: Hi Suddes, 29/6/6 + 3 working days gives 4-Jul, because the following three working days are 30/6, 3/7, 4/7 being 1/7, 2/7 Saturday and Sunday. The formula is (if the date to be increased is in A1) : =WORKDAY(A1,3) Format the result cell like dd-mmm h:mm Regards, Stefi "Suddes" ezt írta: Hello all, i am sure this is very simple but..... I am trying to add 3 working days to a date and time and have the answer displayed in dd/mmm hr:mmm format so if i add 3 working days to 29/6/6 9:00 i would have the answer retrned as 3-Jul 9:00 Thanks for your guidence Suddes |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding on a 24 hour clock basis
Stefi & Bob this is perfect
Many many thanks Suddes "Bob Phillips" wrote: How silly. Try this =WORKDAY(A1,3)+MOD(A1,1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Suddes" wrote in message ... Hi Stefi I tried this and changed the cell format but the result dse not display the time part ie 29/6/6 09:00:00 plus 3 working days should return 04/7/6 09:00:00 but i get 04/07 00:00 it does not seem to be looking at the hours?! any advice Thanks "Stefi" wrote: Hi Suddes, 29/6/6 + 3 working days gives 4-Jul, because the following three working days are 30/6, 3/7, 4/7 being 1/7, 2/7 Saturday and Sunday. The formula is (if the date to be increased is in A1) : =WORKDAY(A1,3) Format the result cell like dd-mmm h:mm Regards, Stefi "Suddes" ezt Ă*rta: Hello all, i am sure this is very simple but..... I am trying to add 3 working days to a date and time and have the answer displayed in dd/mmm hr:mmm format so if i add 3 working days to 29/6/6 9:00 i would have the answer retrned as 3-Jul 9:00 Thanks for your guidence Suddes |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
| Convert decimal hour into time format? | Excel Discussion (Misc queries) | |||
| Decimal to 24 hour clock please. | Excel Worksheet Functions | |||
| Production CLock | Excel Worksheet Functions | |||
| subtracting times using 24 hour clock | Excel Worksheet Functions | |||
Hybrid Mode
