how to generate a number from two dates

Question

Level 1

11 points

how to generate a number from two dates

I am 'new' at this of using Numbers for anything. What I want to do AND I can't seem to figure it out, is to generate a 'days total' from two dates. I get paid S.S. on the 2nd Wednesday of each month. I have X number of dollars to spend. In order to view each day how much money I have for the rest of the month, I need to be able to obtain how many days are left in the period. I know what the fixed day is for the end and could key that in. I want it to be able to take the current day for the calculation without my having to key IT IN each day. Then I could use that returned number for other functions. I did look into "Functions" screen on the right hand side, read them several times, tried some but have not succeeded. Can someone here help this 68 year young man to figure this out? Thanks

Posted on Apr 20, 2015 7:15 AM

Reply

Answer 1

Best reply

Yellowbox

Level 7

20,383 points

Apr 20, 2015 7:52 AM in response to dalleng

Hi dalleng,

Try the TODAY function in a formula.

I don't know of an easy way to calculate the second Wednesday of a month. I had to refer to a calendar and enter the Pay Dates in Column B 😉.

Formula in C2 (and Fill Down)

=B2−TODAY()

April is -13d (13 days ago).

Next Pay Date (in May) is 22 days from now.

Regards,

Ian.

Reply

Answer 2

t quinn

Level 5

7,043 points

Apr 20, 2015 7:54 AM in response to dalleng

Hi dalleng,

The TODAY() function may be what you want. You can subtract it from a date and get a duration.

quinn

Reply

Answer 3

Apr 20, 2015 8:01 AM in response to dalleng

you can use the function TODAY() to get the date.

then you can compute a duration (the difference between two dates). Data/time is a single type of variable in Numbers. Meaning that a complete date/time value includes both a date and a time. When you enter one without the other Numbers assumes the other portion.

you can subtract any two dates to get the duration.

The format for a date is:

1/2/2015 12:09A

2 Jan 2015 12:09A

a duration would look like:

3w 4d 6h 15m 45s 76ms

Three weeks, four days, six hours, fifteen minutes, forty five seconds, seventy six milliseconds

you can use any combinations of these parts for a duration

A1

=TODAY()

B1

=EOMONTH(TODAY(),0)

C1

=B1−A1

Reply

Answer 4

Hiroto

Level 5

7,461 points

Apr 22, 2015 5:49 AM in response to dalleng

Hello

You might try something like the following table. It will calculate the number of days till next 2nd Wednesday.

Table 1 A1 today A2 =TODAY() B1 2nd wednesday B2 =EOMONTH(A2,-1)+1+MOD(9-WEEKDAY(EOMONTH(A2,-1)+1,3),7)+7 C1 next 2nd wednesday C2 =IF(A2<=B2,B2,EOMONTH(A2,0)+1+MOD(9-WEEKDAY(EOMONTH(A2,0)+1,3),7)+7) D1 days till next 2nd wednesday D2 =DUR2DAYS(C2-A2)

* Table is built with Numbers v2.

Good luck,

H

Reply